Outer Join with Parameters...Not Getting Enough Results! 1

[kmkland]

I created a query to list each code and the count of each code between certain date parameters. I created this query using all the fields that I need from 3 different tables:

SELECT tblEmpInfo.[EmpFile#], tblAbsences.Code, tblAbsences.Date_of_Absence
FROM (tblEmpInfo INNER JOIN tblHireDates ON tblEmpInfo.[EmpFile#] = tblHireDates.[EmpFile#]) INNER JOIN tblAbsences ON tblEmpInfo.[EmpFile#] = tblAbsences.[EmpFile#]
WHERE (((tblHireDates.Date_of_Separation) Is Null));

Then, I created an outer join query:

SELECT tblReasonsForAbsences.Code, Count(qryCountAbsJoin.[EmpFile#]) AS [CountOfEmpFile#]
FROM tblReasonsForAbsences LEFT JOIN qryCountAbsJoin ON tblReasonsForAbsences.Code = qryCountAbsJoin.Code
GROUP BY tblReasonsForAbsences.Code;

This works brilliantly!...

[b]Code	CountOfEmpFile#
10	1
11	1
12	1
13	1
14	1
15	1
16	1
17	1
18	0
19	0
20	0
21	19
22	10
23	2[/b]

However... when I add my date parameter:

SELECT tblReasonsForAbsences.Code, Count(qryCountAbsJoin.[EmpFile#]) AS [CountOfEmpFile#]
FROM tblReasonsForAbsences LEFT JOIN qryCountAbsJoin ON tblReasonsForAbsences.Code = qryCountAbsJoin.Code
WHERE (((qryCountAbsJoin.Date_of_Absence) Between [Forms]![frmParameters]![StartDate] And [Forms]![frmParameters]![EndDate]))
GROUP BY tblReasonsForAbsences.Code;

...only non-zero counts are listed:

[b]Code	CountOfEmpFile#
10	1
11	1
12	1
13	1
14	1
15	1
16	1
17	1
21	19
22	10
23	2[/b]

Also, I will be basing a report off of this query.

What should I change???

Thanks in advance!!!!

Rgds,
Kmkland

 

[PHV]

Change the WHERE clause to this:
WHERE qryCountAbsJoin.Date_of_Absence Is Null OR (((qryCountAbsJoin.Date_of_Absence) Between [Forms]![frmParameters]![StartDate] And [Forms]![frmParameters]![EndDate]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OnTheFly]

Or Move the where to the qryCountAbsJoin query instead on the final query.

Hope this helps.

OnTheFly

 

[kmkland]

PH,
THANK YOU TREMENDOUSLY!!!!!!

Kindest Regards,
Kmkland