Can query be grouped by month AND quarter at same time?

[MaddiMond]

Is it possible to group a query at the same time by month and quarter or do I have to create two separate queries and in the report a subreport showing numbers for the quarter? I was asked to create a report that shows numbers by month and accumulated by quarter. Do I have to create subreports to solve this?
Thanks.

Maddi

 

[Remou]

Hi
How about creating a query that shows the date field twice? You can then use one occurence to group by quarter and the other to group by month.

 

[PHV]

You may consider an union query:
SELECT 'byMonth' AS Period, DateSerial(Year([Date field]),Month([Date field]),1) AS FirstDay, Sum([Amount field]) AS Total
FROM yourTable
GROUP BY DateSerial(Year([Date field]),Month([Date field]),1)
UNION SELECT 'byQuarter', DateSerial(Year([Date field]),1+3*((Month([Date field])-1)\3),1), Sum([Amount field])
FROM yourTable
GROUP BY DateSerial(Year([Date field]),1+3*((Month([Date field])-1)\3),1)

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