Solved

How to create running sum in microsoft access

Posted on 2010-11-09
15
407 Views
Last Modified: 2013-11-27
How to create running sum in microsoft access
0
Comment
Question by:peatmilet
  • 7
  • 6
  • 2
15 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34097590
In a query?  On a report?  In a form?
0
 
LVL 75
ID: 34097594
In a Report?  If so, in a text box control, set the Running Sum property to Over Group or Over All

mx

0
 

Author Comment

by:peatmilet
ID: 34097743
Sorry for lack of precision; in a querry.
0
 
LVL 75
ID: 34097785
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 34097817
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
 

Author Closing Comment

by:peatmilet
ID: 34098052
Thank you very much for quick turn arround!
0
 
LVL 75
ID: 34098139
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34099291
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
 
LVL 75
ID: 34099307
Ahh ... not quite following ?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34101796
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34102176
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
 
LVL 75
ID: 34103904
"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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34104200
No, I did not run the subquery code, as I figured the self-join might be faster :)
0
 
LVL 75
ID: 34104231
If you have time, try just to confirm my results ... good info to know.

I will try your test also.

mx
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34104826
Interesting.  The subquery one ran in just a few seconds for me, while the self-join version runs at ~2 minutes...
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Get the ms access version 4 34
Input box criteria 3 19
MS Access Bound Objects. 6 28
Multiple queries for a form 14 22
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now