Query Subtotals???

[claysea]

I have the following information in a query:

ID Name Date Amount
12-100 John Doe 1/16/2012 $126.50
12-100 John Doe 1/26/2012 $126.50


12-100 Jane Doe 1/15/2012 $150.32
15-100 Jane Doe 1/20/2012 $138.00
15-100 Jane Doe 1/31/2012 $126.30
21-100 Jim Doe 1/02/2012 $105.00
21-100 Jim Doe 1/10/2012 $126.30

I am trying to find a way to get the query to subtotal each person's bill based off of their ID. I know it can be done in a report, but I am trying to do it in a query because I need to export it to excel afterwards to send to someone else in my office.

P.S. I don't know how to do macros or unions...I am a novice.

[jzwp11]

You would need a simple aggregate query. The SQL text of the query would look like this

SELECT ID, [name], Sum(Amount) as SumOfAmount
FROM YourTableName
GROUP BY ID, [Name]


You would not include the date field in the aggregate query.

If you want to do this using the query design grid, select the table and the 3 fields above. Right click on the amount field and select totals from the menu. Change the Group By row for the amount table to SUM. That should give you the equivalent to what I show above.

A word of caution, the word "name" is a reserved word in Access so it should not be used as a field or table name. I would strongly recommend you change that field name.

[orange]

Wouldn't/Couldn't/shouldn't you just give the raw data and let the excel user do the subtotals?

[pbaldy]

Again:

https://www.accessforums.net/showthr...total-on-Query

If you want both detail and subtotals in the query, I think you have to go this route.