[AccessD] Running sum in a query without DSUM

The fastest way so far was just to do a make table from the query and then
write code to do the math and place the results in another field.  It's not
pretty, but real fast. The table size is about 100,000 records, If it was
millions, I'd go another route.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: Friday, October 27, 2006 4:38 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Running sum in a query without DSUM

Slow is a relative term. Once upon a time, I used to get a 3 hour 
turnaround on
500 line compiles. How many records and how long?

You might get some more speed by doing the calculations on an array
after using GetRows but that might be limited by table size and RAM

You might get more speed if you specify a linked table or query, with
 OpenRecordset creates a dynaset-type Recordset Try selecting a 
nonupdateable
cursor using dbOpenForwardOnly instead of default dbOpenDynaset


SQL Server: Calculating Running Totals, Subtotals and Grand Total 
Without a Cursor

http://www.databasejournal.com/features/mssql/article.php/3112381

John Skolits wrote:

>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;
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada

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