Running totals in a query

[Guest]

Can this be done? If so, could I get some direction?

I have a table with Route, Category and Sales as column headers. I want to
create a query that gives me (by route) the percentage of total sales per
category. When I click the "Totals" button, I'm given all the ways to
perform totals; one of these options is "Expression". In essence, I want to
total the sales by route and then divide the individual categories within the
route to determine what the percentage of that category is. Is this possible?

 

[Michel Walsh]

Hi,


is it what you are looking for?


---------------------------------

SELECT a.route,
SUM(sales),
SUM(sales) / LAST(TheTotal)
FROM myTable AS a
INNER JOIN (SELECT route, SUM(sales) As TheTotal
FROM myTable
GROUP BY route) AS b
ON a.route=b.route

GROUP BY a.route, category

------------------------------------


The inner most query can be written as a "saved" query. It just compute the
SUM(sales), per route. The join makes the appropriate match to allow us to
compute the ration of the sum(sales) per route, per category, over the
sum(sales) per route.

The SYNTAX forces us to either aggregate the value TheTotal, or to push it
into the GROUP BY clause. Since this is just the same value carried over all
the record, for the same route value, we aggregated with LAST. We could have
used MAX also.



Hoping it may help,
Vanderghast, Access MVP