peatmilet
asked on
How to create running sum in microsoft access
How to create running sum in microsoft access
In a query? On a report? In a form?
In a Report? If so, in a text box control, set the Running Sum property to Over Group or Over All
mx
mx
ASKER
Sorry for lack of precision; in a querry.
Well, this KB:
http://support.microsoft.com/?kbid=210338
describes how to do this on a Form. You might be able to adapt this to a query, calling the function from a query expression instead.
matthewspatrick might have a clever solution using SQL as well ...?
mx
http://support.microsoft.com/?kbid=210338
describes how to do this on a Form. You might be able to adapt this to a query, calling the function from a query expression instead.
matthewspatrick might have a clever solution using SQL as well ...?
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for quick turn arround!
FYI ... I just ran that exact query on a table with 189K records, using a Name field (Windows User Name), sequential ID field (AutoNumber) and a two digit Qty field.
After approx 5 minutes @ 75-80% CPU resources being used, I finally had to hit Control+Break to stop the query, which never completed.
mx
After approx 5 minutes @ 75-80% CPU resources being used, I finally had to hit Control+Break to stop the query, which never completed.
mx
MX,Out of curiosity, how does this one do on that benchmark?Patrick
SELECT t1.Name, t1.Qty, Sum(t2.Qty) AS RunningTotal
FROM SomeTable t1 INNER JOIN
SomeTable t2 ON t1.Name = t2.Name And t2.ID <= t1.ID
GROUP BY t1.Name, t1.Qty
ORDER BY t1.Name, t1.ID
Ahh ... not quite following ?
What I meant was, is the performance any better when instead of using a subquery, we use a self-join with GROUP BY.BTW, the SQL statement above had an error; it should have been:
SELECT t1.ID, t1.Name, t1.Qty, Sum(t2.Qty) AS RunningTotal
FROM SomeTable t1 INNER JOIN
SomeTable t2 ON t1.Name = t2.Name And t2.ID <= t1.ID
GROUP BY t1.ID, t1.Name, t1.Qty
ORDER BY t1.Name, t1.ID
Joe,I just ran the code above against a table of dummy data with 300,000 rows. It took not quite 2 minutes to complete.Did you index the Name field? Without an index there, Jet/Ace is going to have one hell of a time with it...Patrick
"I just ran the code above"
But did you run the original code - the subquery approach ?
I'm sure a self join is going to be faster than a subquery.
Yes, the Name field was indexed.
mx
But did you run the original code - the subquery approach ?
I'm sure a self join is going to be faster than a subquery.
Yes, the Name field was indexed.
mx
No, I did not run the subquery code, as I figured the self-join might be faster :)
If you have time, try just to confirm my results ... good info to know.
I will try your test also.
mx
I will try your test also.
mx
Interesting. The subquery one ran in just a few seconds for me, while the self-join version runs at ~2 minutes...