Totals Query grouped by Month and Year

[Ray Spackman]

First of let me apologize if this has already been covered for I have searched extensively and cannot seemed to find what I am looking for.

I have a table, "tblDailyInfo", with a date field [EntryDate] formatted as date/field in long format, and a currency field [Cash]. In playing around witht he totals, I can get only the totals for [Cash] for each given day within the month and year. What I cannot seem to get figured out is how to get the query to total all the [Cash] records within the month and year, and show that cumulatice total grouped by month and year. Example: (although actual days and dates may not be correct.

What i am getting;
Thurday, August 12, 2011 $10.00
Friday, August 13, 2011 $10.00
Saturday, August 14, 2011 $10.00

What I need to get:

Aug 2011 $30.00

Can anyone help please.

 

[PNGBill]

You can create a derived field in your query which is just Month and year.
Then group on that.

 

[Ray Spackman]

Thank You, can you show me how that format would look? And by derived. I'm thinking you are talking about alias fields?

 

[vbaInet]

format([DateField], "mmm yyyy")

 

[PNGBill]

Do a select query and you will see your Dates as day, month and year.

To group on this field of course you can only group by Day.

You can add a new field to your query eg EntryMonth: Format(Month([EntryDate]))

This should give you a field with just the month number.

Group on this to Sum the days of the month sales.

Add another field for Year and then join them. Group on this field.

You can just format with one field for Month and Year but I forget how

This sql has StatementDate and the three derived fields.

SELECT tblBankStatements.StatementDate, Format(Month([StatementDate])) AS EntryMonth, Format(Year([StatementDate])) AS EntryYear, [EntryMonth] & " " & [EntryYear] AS EntryMonthYear
FROM tblBankStatements
ORDER BY tblBankStatements.StatementDate DESC;

 

[Ray Spackman]

Thank you very much. Got it.