Query to subtotal by month

[BrownBoy]

Hi,

I have table of data that has a list of transactions by date. I'm trying to create a query that subtotals the transactions by year and month. Do any of you have a suggestion. Thanks.

[BrownBoy]

Paul,

Thanks. I agree with you on that, however, I will be combining multiple queries into a master query that I will using in an Excel pivot table. The problem is some of tables already have transaction data by month while others do not. I want all the individual queries to be subtotaled beforehand for the sake of simplicity.

[Ron Coderre]

If you don't want to use an Access report....here's what I'd do:
Create 3 queries
1) Simply lists the details...with an added sort-reference column for YEARMTH with a zero appended.
Example: 2014010

2) Create a query that sums by month......with an added sort-reference column for YEARMTH with a 1 appended.
Example: 2014011

3) Create a query that sums by year......with an added sort-reference column for YEARMTH with a 999 appended to YEARMTH.
Example: 2014999

Last, create a union query that combines those 3 queries and sorts by YEARMTH.
Continuing the example, those columns will sort this way
2014010 <-Jan Detail
2014010 <-Jan Detail
2014010 <-Jan Detail
2014010 <-Jan Detail
2014011 <-Jan Month total
2014020 <-Feb Detail
2014020 <-Feb Detail
2014020 <-Feb Detail
2014021 <-Feb Month total
2014999 <-Year total

Is that something you can work with?

[BrownBoy]

I see where your going... let me put it together and then we will see. Thanks.

[BrownBoy]

Ron,

Sorry it took so long to get back to you. I went with your Union query idea and got exactly what I needed. I'll export to Excel and group it there. Thanks.

[Ron Coderre]

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)