A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access Subtotal in Query



 
 
Thread Tools Display Modes
  #1  
Old June 6th, 2007, 07:44 PM posted to microsoft.public.access.queries
Scott
external usenet poster
 
Posts: 1,119
Default 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  
Old June 7th, 2007, 01:33 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old May 12th, 2010, 05:48 AM posted to microsoft.public.access.queries
kanyuta akkharakun
external usenet poster
 
Posts: 1
Default 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  
Old May 12th, 2010, 11:36 AM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:08 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.