Trouble creating a query to pull the info I need

[Catrina]

I am working on a payroll application. I am trying to generate reports and I'm having trouble creating a query to pull the info I need for one report.

I have all employees in a Master table. One of the fields is the Paycode which tells if an employee is Hourly or Salary.

The payroll hours are entered into another EmployeeHours table.

In my report I need to print all the Paycode 2(Salary) people (which is no problem) as Normal Salary unless hours have been entered for them (here is the problem), for this I need the information from the EmployeeHours table (records are not output for Salary people unless they have a change in Normal Salary), and I only want the employee printed once, either as Normal Salary or with the inputted hours.

I can not figure out how to create a query pulling all Paycode 2 people, with and without(again, records are not output for Salary people unless they have a change in Normal Salary)hours together.

Ex:
Emp#.......Reg Hrs

1..........Normal Salary (Would be pulled from Master as a Salary Person)
2..........42.00 (would be pulled from EmployHours as a change in normal)
3..........Normal Salary
4..........Normal Salary
5..........48.00

etc

I need to create a general query in the Access database to create the report, then in my code I want to sort it in the order the user wants (Alpha, Numeric etc).

At least this is how I have done the other reports

Hope this made at least a little sense. Thanks in advance for any help

Catrina

 

[jeba]

You need a LEFT JOIN type of query.

SELECT Master.*, Hours.*
FROM Master LEFT JOIN Hours ON Master.numid = Hours.t2numid
WHERE Master.Paycode = 2 ORDER BY yoursequences;

This will give you ALL employee masters of type 2. If there is no matching Hours record, those fields will be null.

Check for null to bypass those on your report.

If you have more than one Hours record for a master, the result table will have 1 record for each Hours record. The master info will be duplicated into each record.

 

[Catrina]

Thank you, got that working, now one more question. The Null fields mean Normal Salary will be paid, and I need the report to print "Normal Salary". How and where do I check for the null fields. Here is what I tried, but it did not work. I'm not sure how to find the Null fields.

SQL$ = "SELECT Rates.*,EmployeeHours.* FROM "
SQL$ = SQL$ & "Rates LEFT JOIN EmployeeHours ON "
SQL$ = SQL$ & "Rates.PAYEMP = EmployeeHours.PAYEMP "
SQL$ = SQL$ & "WHERE Rates.PAYCDE = '2' OR Rates.PAYCDE = '3' "
SQL$ = SQL$ &amp; &quot;AND DeleteRec <>'D' &quot;
SQL$ = SQL$ &amp; &quot;ORDER BY Rates.PAYEMP asc&quot;

DtaReports.rscomSalary.Open SQL$
If DtaReports.rscomSalary.Fields = &quot;&quot; Then (error here)
RptSalary.Sections(1).Controls(2).Visible = True
RptSalary.Sections(1).Controls(2).Caption = &quot;Normal Salary&quot;
End If

RptSalary.PrintReport

The report works when I leave out the If statement, but prints blank lines for the employees with no input, I need the Normal statement there.

Thank again for the previous help.

Catrina

 

[jeba]

A Null and a null string are 2 different things.

The following statement:

If fielda = &quot;&quot; Then ' is a check for a null string value in a string field.

Empty fields from the result of a left join return Null values.

SO, you need to check any field from the EmployeeHours table for a Null value, like this:

If isNull(EmployeeHours(&quot;somefield&quot) = true Then
print.

I think it also works without the &quot;= true&quot; part.

You might also be able to say:

If EmployeeHours(&quot;somefield&quot = Null Then
(not sure about this one)

Try them all!

Jim

 

[jeba]

Just noticed your WHERE clause.

You need to be careful when you mix AND's and OR's.

Conditions are split on OR's and combined on AND's. I'm not sure your statement is what you really want.

It will return ALL records with paycode '2' (regardless of the value of DeleteRec OR
all records that have paycode = '3' AND DeleteRec <> 'D'

If you actually want paycode = '2' and DeleteRec = 'D'
OR
paycode = '3' and DeleteRec = 'D'
then you need to code it that way.