Running Total in a query

[Nathan]

I have read similiar threads on this subject but I just cannot get the syntax
to work out correctly - I have a lists of items (SKU) and how often they are
picked from the warehouse, along with a area of the warehouse where thay are
associated (F) - I am trying to get a running total of the PctofPicks field
based on the the warehouse area (F). I have no trouble doing this in a
report, but I needs the results to feed another query.

Here is the the select query statement which I have sorted in descending
order by the high volume items :

SELECT qrySumFullCubePicks.SKU, qrySumFullCubePicks.Description,
qrySumFullCubePicksbyArea.F, qrySumFullCubePicks.SumOfCubePicked,
qrySumFullCubePicks!SumOfCubePicked/qrySumFullCubePicksbyArea!SumOfCubePicked
AS PctofPicks
FROM qrySumFullCubePicks INNER JOIN qrySumFullCubePicksbyArea ON
qrySumFullCubePicks.F = qrySumFullCubePicksbyArea.F
ORDER BY qrySumFullCubePicksbyArea.F,
qrySumFullCubePicks!SumOfCubePicked/qrySumFullCubePicksbyArea!SumOfCubePicked
DESC;

Can someone help me out on the proper sytax to get a running total of the
PctofPicks field, or if it is possible to feed report results back into a
query (??) that would work as well, as I have accomplished this in a report.

Thank you

 

[Michel Walsh]

A possible solution for a running sum is to use the table twice, but in one
of its reference, to limit it to the value "before", "less than" the value
of the other instance of the table:


SELECT a.area, a.siteID, SUM(a.someValue), SUM(b.someValue) AS runningSum
FROM table AS a INNER JOIN table AS b
ON a.area = b.area AND a.siteID >= b.siteID
GROUP BY a.area, a.site


so, you see the two references, a and b, to the same table, 'table', with
records of 'b' being limited to be of the same area of those of reference
'a', but 'b' is also limited to those sites that are "less than", or equal
to, a given site actually picked by reference 'a'.



Hoping it may help,
Vanderghast, Access MVP