Refer to Total in query


G

Guest

I need to run a query where one of the values should be a Percentage of the
bottom total (not the "right" total). It's kind of an ABC analisys.

Almost every sql language allows that. Access doesn't?

If it does, how?

Example:

Person Sales %Total Sales
xpto1 10 10/Total Sales
 
Ad

Advertisements

J

John Spencer

You could use DSUM function or a subquery to get the value

SELECT Person
, Sum(Sales) as SalesSum
, Sum(Sales)/DSUM("Sales","YourTable") as PercentTotal
FROM YourTable
GROUP BY Person

The following should also work for you and is probably more efficient.

SELECT Person
, Sum(Sales) as SalesSum
, Sum(Sales)/(SELECT Sum(Sales) FROM YourTable) as PercentTotal
FROM YourTable
GROUP BY Person

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

referring to a totals query on a form 2
Totals Query 1
totals query 1
Totals Query 10
Totals Query 2
Totals Query 3
Query Totals 3
Query Totals 1

Top