Running total

[Beagle1927]

Is there anyway to produce a running total in a query? I tried the
help menus but they only mention running totals in reports.

 

[Michel Walsh]

You have to have a field that supply the order (like a dateStamp) and, sure,
the value to be summed.


SELECT a.dateStamp, LAST(a.theValue), SUM(b.theValue)
FROM yourTable AS a INNER JOIN yourTable AS b
ON a.dateStamp >= b.dateStamp
GROUP BY a.dateStamp



will then do.


Hoping it may help,
Vanderghast, Access MVP

 

[Beagle1927]

Hello,


I cannot get it to work....


I need to calculate a running total on Total_Cost field on the
TLB_Sales Table by customer.

Ie. Customer A Total Cost =$125 Running total = $125
Customer B Total Cost =$100 Running total=$225


Is this possible?

 

[Michel Walsh]

Replace the dateStamp by the field with the customer.


In Northwind, that works fine:


SELECT a.ProductName, Last(a.UnitPrice), Sum(b.UnitPrice)
FROM Products AS a INNER JOIN Products AS b
ON a.ProductName >= b.ProductName
GROUP BY a.ProductName;



Vanderghast, Access MVP