Query design question (Group by Month)

[jonathanchye]

Hi all,

I am trying to build a query to be used in a report. This query would be based on a single table. In this table there is a field called "DateOfEnquiry". I want to first group this monthly and also show total for each month. I then want to group them yearly and show month statistics for past years too.

At the moment I can build a simple query using the Query Builder showing each enquiries for any month I want by using Month([DateofEnquiry])=3 for example.

However, I have no idea how to sum them up or even show for every other month. Must I build a separate query for every month? I added another field to Count the enquiries grouped by Enquiry number but it just shows a 1 next to each of the DateofEnquiry records...

Any help/advice would be kindly appreciated. Thanks.

 

[pbaldy]

This would sum by month:

SELECT Format(DateField, "yyyymm") AS MonthGroup, Sum(AmountField) As TotalAmount
FROM TableName
GROUP BY Format(DateField, "yyyymm")