create a faster running sum query (1 Viewer)

Status
Not open for further replies.

CJ_London

Super Moderator
Staff member
Local time
Today, 11:05
Joined
Feb 19, 2013
Messages
12,585
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
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom