Summarize data

[jgnasser]

I have this simple table with three fields - Date, User and Amount. I want a summary to show the total amount per usser per month. How do I do this?

 

[mresann]

Create a new query as if you were going to make a normal select query on those three fields. In the design grid, click the Sum button (the button that looks like a Greek "E"). The grid will change and give you aggregrate function choices ("Total" row). Leave "Group By" for the date and the user, and change the total on amount to "Sum". You should get what you requested.

Check out the other aggregate functions you can use. I'm sure you can find other uses in your other query requirements.

 

[pbaldy]

SELECT User, Format([date],"yyyy/mm") AS TheMonth, Sum(amount) AS SumOfamount
FROM TableName
GROUP BY User, Format([date],"yyyy/mm");

Date should not be used as a field name by the way; it can get confused with the Date() function.

 

[jgnasser]

Thanks, this worked as intended.