Go Back   Access World Forums > Microsoft Access Reference > Code Repository

Thread Tools Rate Thread Display Modes
Old 03-23-2018, 06:44 AM   #1
Super Moderator
Join Date: Feb 2013
Location: UK
Posts: 10,685
Thanks: 40
Thanked 3,465 Times in 3,354 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
create a faster running sum query

There is often a requirement to create a running sum (for balance after each transaction for example) for a form, unfortunately this is not a feature for forms, only reports.

This can be done in the underlying query and typically you will see the use of dsum or a subquery.


SELECT prodcode, trandate, DSum("Amount", "myTable","trandate<=" & trandate) as RunSum
FROM myTable
ORDER BY prodcode, trandate

SELECT prodcode, trandate, (SELECT Sum(Amount) FROM myTable T WHERE prodcode=myTable.prodcode and trandate<=myTable.trandate) as RunSum
FROM myTable
ORDER BY prodcode, trandate
the subquery will be faster than the dsum but can still be excruciatingly slow for large datasets, particularly if the criteria is more complex, for example summarising stock transactions by stock item supplier and other factors.

There is a third way. It has to be done in the sql window because the required join cannot be representing in the query design window. However there is a small cheat which means you can create the bulk of the query in the design window and then go to the sql window and make a small change

in the design table, select your table twice so you have myTable and myTable_1

using the above example, join the two tables on prodcode and trandate

select prodcode and trandate from myTable and Amount from myTable_1

change to a group by query and group by prodcode and trandate and sum amount.

order by prodcode and trandate.

Now go into the sql window and where you have in the join


change to


or for slightly better clarity


save and run the query.

If you try to open in the query design window, you will lose the join and need to recreate it as above. Based on the above the final sql would be

Select myTable.Prodcode, myTable.trandate, sum(myTable_1.amount) AS RunSum
FROM myTable INNER JOIN myTable_1 ON (myTable.Prodcode=myTable_1.prodcode and myTable_1.trandate<=myTable.tranDate
GROUP BY myTable.Prodcode, myTable.trandate
ORDER BY myTable.Prodcode, myTable.trandate
I haven't timed it but a recent query I was asked to improve based on a sub query on a dataset of 100,000 records was still running after 45 minutes. Implemented the join method resulted in the query taking less than a minute.

I always index appropriately so if you haven't indexed you may not achieve this level of improvement

A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following 5 Users Say Thank You to CJ_London For This Useful Post:
Cosmos75 (04-18-2018), isladogs (06-03-2018), jdraw (04-02-2018), Minty (03-23-2018), NauticalGent (03-30-2018)

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Will pass through query be faster? GBalcom General 4 11-18-2014 08:55 PM
Running Sum Query to create Graph / Chart Cosmos75 Queries 35 06-29-2010 05:54 AM
Running a query from a form to create a report SparkySX Queries 4 07-21-2009 03:50 AM
How To Make Query Run Faster jereece Queries 5 03-26-2008 10:45 AM
How to make a query run faster neoklis General 1 05-09-2007 02:31 AM

All times are GMT -8. The time now is 04:01 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World