By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Access Query Help

P: 3
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
Jun 6 '07 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 218
Could you please post your existing code for the query...
Jun 6 '07 #2

P: 3
SELECT DISTINCTROW Projects.Location, Projects.Sponsor, Sum([2007 Budget].Jan) AS SumOfJan
FROM [2007 Budget] RIGHT JOIN Projects ON [2007 Budget].[Project #] = Projects.[Project #]
GROUP BY Projects.Location, Projects.Sponsor;

Could you please post your existing code for the query...
Jun 6 '07 #3

Expert 100+
P: 218
SELECT DISTINCTROW Projects.Location, Projects.Sponsor, Sum([2007 Budget].Jan) AS SumOfJan
FROM [2007 Budget] RIGHT JOIN Projects ON [2007 Budget].[Project #] = Projects.[Project #]
GROUP BY Projects.Location, Projects.Sponsor;
Have a look at the COMPUTE clause - it may help.

Are you using Access or SQL to store your data?
Jun 6 '07 #4

P: 3
Access. I just pulled the sql statement that I designed using access.


Have a look at the COMPUTE clause - it may help.

Are you using Access or SQL to store your data?
Jun 6 '07 #5

Expert 100+
P: 218
I asked about SQL cos I know Compute works in SQL.

Have you thought about using the VB DSum function? You don't say in what context you are using your query...
Jun 6 '07 #6

Expert 100+
P: 218
BTW

How does your query work if you add ", Sum([2007 Budget].Jan)" to the Group By clause?
Steve
Jun 6 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.