If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access Subtotal in Query
I have a simple query that uses 3 fields. Location, Person and Value. When I
group by location and person and sum value, I get all the results I need but I would love to see subtotals for each location. I'm using Access 2003. How do I add in the subtotals. Something like Maryland, Person 1, 5 Maryland, Person 2, 10 Maryland Subtotal, 15, - this is the line I'm trying to get New York, Person 1, 20 |
#2
|
|||
|
|||
Access Subtotal in Query
Do this in a report.
In report design view, open the Sorting And Grouping box (View menu.) Choose the Location field, and in the lower pane of the dialog, set Group Footer to Yes. You can now add a subtotal to the group footer section. The Control Source will be something like this: =Sum([SumOfValue]) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scott" wrote in message ... I have a simple query that uses 3 fields. Location, Person and Value. When I group by location and person and sum value, I get all the results I need but I would love to see subtotals for each location. I'm using Access 2003. How do I add in the subtotals. Something like Maryland, Person 1, 5 Maryland, Person 2, 10 Maryland Subtotal, 15, - this is the line I'm trying to get New York, Person 1, 20 |
#3
|
|||
|
|||
subtotal in access?
Subtotal ?? Access ???????????
scot wrote: Access Subtotal in Query 06-Jun-07 I have a simple query that uses 3 fields. Location, Person and Value. When I group by location and person and sum value, I get all the results I need but I would love to see subtotals for each location. I'm using Access 2003. How do I add in the subtotals. Something like Maryland, Person 1, 5 Maryland, Person 2, 10 Maryland Subtotal, 15, - this is the line I'm trying to get New York, Person 1, 20 Previous Posts In This Thread: On Wednesday, June 06, 2007 2:44 PM scot wrote: Access Subtotal in Query I have a simple query that uses 3 fields. Location, Person and Value. When I group by location and person and sum value, I get all the results I need but I would love to see subtotals for each location. I'm using Access 2003. How do I add in the subtotals. Something like Maryland, Person 1, 5 Maryland, Person 2, 10 Maryland Subtotal, 15, - this is the line I'm trying to get New York, Person 1, 20 On Wednesday, June 06, 2007 8:33 PM Allen Browne wrote: Do this in a report. Do this in a report. In report design view, open the Sorting And Grouping box (View menu.) Choose the Location field, and in the lower pane of the dialog, set Group Footer to Yes. You can now add a subtotal to the group footer section. The Control Source will be something like this: =Sum([SumOfValue]) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scott" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice IIS 7.0 Extensionless UrlRewriting (Short urls) http://www.eggheadcafe.com/tutorials...nless-url.aspx |
#4
|
|||
|
|||
subtotal in access?
Yes, Reports handle subtotals quite nicely.
If needs must, a union query can also provide subtotals, but performance with large tables might not be to your liking. However, it would look like this (you cannot create union queries in Design View - you must use SQL View): SELECT Location, Person, Sum(Value) As Total, 0 As Sorting FROM tablename GROUP BY Location, Person UNION ALL SELECT Location, 'Subtotal', Sum(Value),1 FROM tablename GROUP BY Location ORDER BY Location,Sorting,Person kanyuta akkharakun wrote: Subtotal ?? Access ??????????? scot wrote: Access Subtotal in Query 06-Jun-07 I have a simple query that uses 3 fields. Location, Person and Value. When I group by location and person and sum value, I get all the results I need but I would love to see subtotals for each location. I'm using Access 2003. How do I add in the subtotals. Something like Maryland, Person 1, 5 Maryland, Person 2, 10 Maryland Subtotal, 15, - this is the line I'm trying to get New York, Person 1, 20 Previous Posts In This Thread: On Wednesday, June 06, 2007 2:44 PM scot wrote: Access Subtotal in Query I have a simple query that uses 3 fields. Location, Person and Value. When I group by location and person and sum value, I get all the results I need but I would love to see subtotals for each location. I'm using Access 2003. How do I add in the subtotals. Something like Maryland, Person 1, 5 Maryland, Person 2, 10 Maryland Subtotal, 15, - this is the line I'm trying to get New York, Person 1, 20 On Wednesday, June 06, 2007 8:33 PM Allen Browne wrote: Do this in a report. Do this in a report. In report design view, open the Sorting And Grouping box (View menu.) Choose the Location field, and in the lower pane of the dialog, set Group Footer to Yes. You can now add a subtotal to the group footer section. The Control Source will be something like this: =Sum([SumOfValue]) -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
Thread Tools | |
Display Modes | |
|
|