Running balances

[tom]

I need running balances.

I have written a query that calculates running balances using the Dsum
function but it runs slow and uses too much computer resources.

Is there some other way to get running balances besides the Dsum function?

 

[Roger Carlson]

You can also use a correlated sub-query. I don't know if it an less
resource intensive, though. I've got a sample on my website
(www.rogersaccesslibrary.com) called "RunningSumInQuery.mdb" that
illustrates both methods. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=279

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ken Sheridan]

You can use a subquery; you might see some improvement in performance, but
its still likely to be slow. If you have a unique date/time value per
transaction then you can base it solely on that:

SELECT TransactionDateTime, Amount,
(SELECT SUM(Amount)
FROM Transactions As T2
WHERE T2.TransactionDateTime <= T1.TransactionDateTime)
AS Balance
FROM Transactions AS T1
ORDER BY TransactionDateTime DESC;

If not, and you only have a date per transaction, then you have to bring
another uniquely valued column such as an autonumber transactionID into play
to separate transactions on the same day:

SELECT TransactionDate, Amount,
(SELECT SUM(Amount)
FROM Transactions AS T2
WHERE T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate))
AS Balance
FROM Transactions AS T1
ORDER BY TransactionDate DESC, TransactionID DESC;

Note that these queries won't be updatable. Calling the DSum function
rather than using a subquery is generally only done if an updatable query is
required.

Ken Sheridan
Stafford, England