Counts and Dates

[Kelly H]

I have a query that is supossed to gather a count of items in each status for
a date range. When I run my query it is giving me a count per day of some
but not all status. How can I have it give me a count for the full date
range?

SELECT [tbl Work Order].[Call Status], Count([tbl Work Order].[Call Status])
AS [CountOfCall Status], [tbl Work Order].[Completed Date]
FROM [tbl Work Order]
GROUP BY [tbl Work Order].[Call Status], [tbl Work Order].[Completed Date]
HAVING ((([tbl Work Order].[Completed Date]) Between [start] And [end]));

 

[John Vinson]

I have a query that is supossed to gather a count of items in each status for
a date range. When I run my query it is giving me a count per day of some
but not all status. How can I have it give me a count for the full date
range?

It's giving you what you're asking for: you're asking it to group by
the date, so it's grouping by each individual date!

Take the date field out of the GROUP BY clause, by changing the
default GROUP BY operator in the grid to WHERE; this will filter the
records first, rather than counting every record in the table and then
discarding those counts which don't match the criterion. The SQL would
be

SELECT [tbl Work Order].[Call Status], Count([tbl Work Order].[Call
Status]) AS [CountOfCall Status]
FROM [tbl Work Order]
GROUP BY [tbl Work Order].[Call Status]
WHERE ((([tbl Work Order].[Completed Date]) Between [start] And
[end]));

John W. Vinson[MVP]