How to create running sum in microsoft access

How to create running sum in microsoft access
peatmiletAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Ask, and ye shall receive.This is a generic example, as the Asker has not provided any hints about his/her data.  It assumes that there is an ID column we can use for the ordering.
SELECT t1.Name, t1.Qty,
    (SELECT Sum(t2.Qty)
    FROM SomeTable t2
    WHERE t2.Name = t1.Name And t2.ID <= t1.ID) AS RunningTotal
FROM SomeTable t1
ORDER BY t1.Name, t1.ID

Open in new window

0
 
Patrick MatthewsCommented:
In a query?  On a report?  In a form?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
In a Report?  If so, in a text box control, set the Running Sum property to Over Group or Over All

mx

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
peatmiletAuthor Commented:
Sorry for lack of precision; in a querry.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
peatmiletAuthor Commented:
Thank you very much for quick turn arround!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
Patrick MatthewsCommented:
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

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Ahh ... not quite following ?
0
 
Patrick MatthewsCommented:
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

Open in new window

0
 
Patrick MatthewsCommented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
0
 
Patrick MatthewsCommented:
No, I did not run the subquery code, as I figured the self-join might be faster :)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If you have time, try just to confirm my results ... good info to know.

I will try your test also.

mx
0
 
Patrick MatthewsCommented:
Interesting.  The subquery one ran in just a few seconds for me, while the self-join version runs at ~2 minutes...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.