Access Subtotal in Query

[scott]

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

 

[Allen Browne]

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])

 

[kanyuta akkharakun]

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:

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

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

Reply to group, rather than allenbrowne at mvps dot org.



Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorial...df-52898c6aa5d7/iis-70-extensionless-url.aspx

 

[Bob Barrows]

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

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:

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

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])