Return COUNT for all items in Table

[faccorp]

I have 2 tables

TYPES (ID, NAME)
SCHEDULE (ID, DATE, TYPE)

I want a query that returns the count of all the Types used in the schedule table for a given time period. Even if the count is zero.

I have:

SELECT TYPES.NAME, COUNT(SCHEDULE.TYPE)
FROM TYPES LEFT JOIN SCHEDULE ON SCHEDULE.TYPE = TYPES.ID
WHERE (SCHEDULE.DATE Between #1/1/2005 And #10/1/2005#)
GROUP BY TYPES.NAME

I can't get this query to return all the TYPES and their count. It only returns the TYPES/COUNT for the TYPES that fall within the time period of the WHERE clause.

If I re-write the query ommiting the WHERE clause

SELECT TYPES.TYPE, COUNT(SCHEDULE.TYPE)
FROM TYPES LEFT JOIN SCHEDULE ON SCHEDULE.TYPE = TYPES.ID
GROUP BY TYPES.TYPE

Then it works correctly. That is, the result looks something like

TYPE1 3
TYPE2 2
TYPE3 0
TYPE4 1
etc.

When I use the WHERE clause, then the result does not return the TYPES that have a count of 0.

Is there another way to write this query where I can specify a date range?



Thanks in advance

 

[flyover789]

something like

SELECT TYPES.NAME, COUNT(SCHEDULE.TYPE)
FROM TYPES LEFT JOIN SCHEDULE ON SCHEDULE.TYPE = TYPES.ID
WHERE (SCHEDULE.DATE Between #1/1/2005 And #10/1/2005#) [blue]OR SCHEDULE.TYPE IS NULL[/blue]
GROUP BY TYPES.NAME

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[faccorp]

Thanks but this seems to only work if a particular type was never used anywhere in the schedule table (not just the result of the WHERE clause).

So if TYPE3 was used in dates prior to 1/1/2005 but not used in the dates after 1/1/2005, then TYPE3 is not returned.

The resulting query needs to return all the TYPES and a count of how many times they were used in the given time period.


Thanks in advance again.

 

[PHV]

And this ?
SELECT T.NAME, Count(S.TYPE)
FROM TYPES As T LEFT JOIN (
SELECT [TYPE] FROM SCHEDULE WHERE [DATE] Between #2005-01-01# And #2005-10-01#
) As S ON T.ID = S.TYPE
GROUP BY T.NAME

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

 

[faccorp]

PH,

Thanks. This worked. For some reason I have trouble adding this query to the database using a DAO QueryDef command but I'll figure that one out. It must have something to do with the temporary tables T and S

Anyway, thanks for the input.