Access Query Help

[smademann]

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

[cyberdwarf]

Could you please post your existing code for the query...

[smademann]

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...

[cyberdwarf]

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?

[smademann]

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?

[cyberdwarf]

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...

[cyberdwarf]

BTW

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