Refer to Total in query

[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

 

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