Grouping Like Dates

[S. Bassette]

I would like group dates together and total all of the other(number) fields
in a query. I would like to do this in quarter year intervals. Any ideas or
thoughts would be useful.

Desired output
Date HrsWrked
Q1 2004 5000
Q2 2004 8045
Q3 2004 6950
Q4 2004 7542
Q1 2005 7548

 

[Jerry Whittle]

The easiest place to group dates by quarter is in a Report. You can use the a
report's sorting and grouping function and specify quarter on a date field.

However you say "total all of the other(number) fields". If you have columns
across with number that need to be added, like a spreadsheet, you'll need to
first sum up these fields in a query then use that query as the record source
for the report. Then do the sorting and grouping there.

 

[KARL DEWEY]

SELECT Format([YourDateField], "Q" & "q" & " " & "yyyy") AS QTR_Year,
Format([YourDateField], "yyyyq"), Sum([YourHoursField]) AS HrsWrked
FROM YourTable
GROUP BY Format([YourDateField], "yyyyq");