Count by Date, Month and Year in one Query

[Medee]

Hi,
I have a table that has a Date field. I need to generate a report for 24 hours, Month to date and Year to date. I have done individual group by queries to count by Date, Month to date and Year to date. The three queries are provided below. Is there a way that I can do this in one query? I have also attached the database that I have been using for this.

Thanks.

24 Hours Query

SELECT Table1.Service, Table1.Profile, Table1.dDate, Count(Table1.dDate) AS datecount
FROM Table1
GROUP BY Table1.Service, Table1.Profile, Table1.dDate
HAVING (((Table1.dDate)=[varDate]));

Month to Date Query

SELECT Table1.Service, Table1.Profile, Month([dDate]) AS mnthNumber, Count([mnthNumber]) AS mnthCount
FROM Table1
GROUP BY Table1.Service, Table1.Profile, Month([dDate])
HAVING (((Month([dDate]))=[varMonth]));

Year to Date Query

SELECT Table1.Service, Table1.Profile, Year([dDate]) AS yearNumber, Count([yearnumber]) AS countYear
FROM Table1
GROUP BY Table1.Service, Table1.Profile, Year([dDate])
HAVING (((Year([dDate]))=[varYear]));
Sample.accdb

[CJ_London]

if you mean one query to show all results, not as such. Use a UNION query to combine the three outputs but note you have two datatypes for column3 - a date and a number, these will need to go into different columns.

You may also require an additional column to provide some sorting ability