Show all fields, even zeros (newbie)

[gfcaim]

This is probably really simple...

I have two tables, one has the work areas (tblAreas), the other has info about what each area produced (tblHeader). They are linked 1 (areas) to many.

I want to write a query that lists all the areas, and a count (not sum) of their data entries, even if that figure is zero (important). I drop into my query the area field from tblAreas and the qty field from tblHeader, then set the qty to count. Then I alter the join properties to left join (show all the records in tblAreas etc). This works fine...

...until i add another field, the date, which is needed because I want to use date as a parameter (show only records from a specific date). It still counts the records correctly but doesn't show all areas, i.e. those counts that are zero.

Any ideas how to put me right?

 

[Mile-O]

Use an LEFT OUTER JOIN

 

[gfcaim]

Er, I'm still missing something...

Thanks for the reply.

I am still missing something though. I altered the query (by typing the 'outer' word in sql view - is there another way of doing this?) but get exactly the same results! The sql now reads:

SELECT tblAreas.Area, Count(tblHeader.Date) AS CountOfDate
FROM tblAreas LEFT OUTER JOIN tblHeader ON tblAreas.Area = tblHeader.Area WHERE (((tblHeader.Date)=#6/22/2004#))
GROUP BY tblAreas.Area;