Need a Count Including Zero

[betheball]

I have 2 tables. One simply lists every day of the year. The other, lists various data, including a date field. I need to create a query that will tell me how many records exist for each day of the year. Not all days of the year exist in the table, but I need to display every date of the year and if there are no records for that date, display 0. I have tried several variations, but cannot get what I need. For example, this:

SELECT Calendar.[Date], COUNT(Calendar.[Date]) AS myCount FROM
(SELECT [Date] FROM Daily WHERE Job = 'Preventive') AS Daily
RIGHT JOIN Calendar ON Daily.Date = Calendar.Date
GROUP BY Calendar.[Date]
HAVING Calendar.[Date] BETWEEN #1/1/2005# AND #1/31/2005#

The above gives me a count of 1 for all dates that do not exist in the table named Daily, instead of a 0.

 

[FoFa]

Try This (May need tweaking)
SELECT Calendar.[Date], COUNT(Daily.[Date]) AS myCount FROM
Calendar
LEFT JOIN Daily ON
(Daily.Date = Calendar.Date AND
Daily.Job = "Preventive")
GROUP BY Calendar.[Date]
HAVING Calendar.[Date] BETWEEN #1/1/2005# AND #1/31/2005#

NOTE - If TIMES are associated with any of your dates, you also may not get the results you want.

 

[betheball]

Brilliant. Plus I learned something. I didn't know you could add a second criteria to the JOIN portion of the query. Thanks for the help.

 

[FoFa]

If you have an outer join (left or right) and you put the criteria in the WHERE clause instead of the Join, you do not always get the results you desire. Always better to put outer join criteria in the join itself.

Example:
LEFT Join T2 ON T1.key = T2.key AND T2.col1 = "DAY"
Works fine

BUT
LEFT Join T2 ON T1.key = T2.key
WHERE T2.col1 = "DAY"

May not return the proper results because when the tables are unjoined, it returns a NULL for T2.col1, and your WHERE clause does not account for that. So for the WHERE to work the same as the criteria in the join, you need to specify;

LEFT Join T2 ON T1.key = T2.key
WHERE (T2.col1 = "DAY" OR T2.col1 IS NULL)

So it is just easier to put in the join criteria.