[AccessD] Running sum in a query without DSUM

Hi John

This method is not using DSum() but SUM of SQL which is much faster. However, at the subquery will run for each and every records there's no way you can retrieve a running sum "fast" for a large recordset.

/gustav

>>> ssharkins at setel.com 26-10-2006 17:09 >>>
Try this one. Let me know if it's any faster, I haven't tried it myself and
would like to know. 


select T1.datafield, (select sum(datafield) from yourtable as T2 where
T2.datafield <= T1.datafield ) as cumsum
from yourtable as T1
order by T1.datafield


Susan H. 


Thanks Jim.
That makes sense but it still essentially does a dsum in the embedded select
statement. It then runs very slowly for large tables.


Here is some a method from a query samples database I got from Lord knows
where. HTH Jim Hale


SELECT [Order Details].OrderID, Sum([UnitPrice]*[Quantity]*(1-[Discount]))
AS OrderTotal, (SELECT Sum(UnitPrice*Quantity*(1-Discount)) FROM [Order
Details] OD WHERE OD.OrderID <= [Order Details].OrderID) AS RunningTotal
FROM [Order Details] GROUP BY [Order Details].OrderID;