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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-23-2018, 06:44 AM   #1
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,895
Thanks: 37
Thanked 3,213 Times in 3,114 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
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.

e.g.

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

Code:
 
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

myTable.TranDate=myTable_1.trandate

change to

myTable.TranDate>=myTable_1.trandate

or for slightly better clarity

myTable_1.TranDate<=myTable.trandate

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

Code:
 
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

__________________
CJ_London
_______________________
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 4 Users Say Thank You to CJ_London For This Useful Post:
Cosmos75 (04-18-2018), jdraw (04-02-2018), Minty (03-23-2018), NauticalGent (03-30-2018)
Reply

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 11:15 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


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