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]));

 

[JHB]

.. Is there a way that I can do this in one query?

Could you show how you want the result?

 

[plog]

Yes. You effctively move the HAVING criteria (which actually should be in the WHERE) to a calculated field and sum up the number of records meeting the criteria. I'll use the 24 hours query demonstrate the method:

SELECT Table1.Service, Table1.Profile, SUM(IIf((Table1.dDate=[varDate]), 1,0) AS dateCount
FROM Table1
GROUP BY Table1.Service, Table1.Profile

Use that query and then just add calculated fields for the month and year as I have done for the date.

 

[jpaokx]

What about using format function? So, you will have format(field, "dd") for day, format(field, "mm") for month and format(field, "yy") for year.

 

[arnelgp]

take a look at this

 

[Medee]

Great. Works perfect.