- Local time
- Today, 22:42
- Joined
- Feb 19, 2013
- Messages
- 16,605
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.
or
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
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
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
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
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