Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

How to create running sum in microsoft access

How to create running sum in microsoft access
0
peatmilet
Asked:
peatmilet
  • 7
  • 6
  • 2
1 Solution
 
Patrick MatthewsCommented:
In a query?  On a report?  In a form?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
In a Report?  If so, in a text box control, set the Running Sum property to Over Group or Over All

mx

0
 
peatmiletAuthor Commented:
Sorry for lack of precision; in a querry.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Patrick MatthewsCommented:
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
 
peatmiletAuthor Commented:
Thank you very much for quick turn arround!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now