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;
----------------------------------------
Use an LEFT OUTER JOIN
----------------------------------------
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?
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;
----------------------------------------
Use an LEFT OUTER JOIN
----------------------------------------
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?
