[AccessD] Running sum in a query without DSUM

I did try this, and it was very slow. The table is very large.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Thursday, October 26, 2006 11:09 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Running sum in a query without DSUM

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;

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com