Access to calculate cumulative sum in Query - DSUM performance very slow

Question

I am designing an Access loan program and need to build up a query to show the cumulative payment amount of principal for subsequent calculation, and I use a function “DSUM” for this purpose. But the performance of this function is very slow and unacceptable for only around 20 loan data so I need to find alternative to replace this function. I know that to show the cumulative sum in report is simple and fast, but I need to show the cumulative sum in query.

Can somebody suggest alternative ways to replace the DSUM function to calculate cumulative sum in Query? Thank you very much!

Answer 1

Can somebody suggest alternative ways to replace the DSUM function to calculate cumulative sum in Query? Thank you very much!

You can use a subquery.

If you need more help then post your query SQL statement.

---

Build a little, test a little

Answer 2

dSum is faster with a well-designed database.  If you are matching on text fields instead of shorter long integers, I would imagine performance would be slow.  Also dSum works faster on indexed fields.

As Karl asked though, it would help to see the SQL, thanks

---

Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

Answer 3

The most efficient method is a join, but the result set is not updatable, for which you will need to call the DSum function.  You'll find examples of both in Balances.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

The following query, for instance, returns the balances for each customer, using a join:

SELECT T1.CustomerID, T1.TransactionDate, T1.TransactionAmount,
SUM(T2.TransactionAmount) AS Balance
FROM Transactions AS T1 INNER JOIN Transactions AS T2
ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
AND (T2.TransactionDate<=T1.TransactionDate) AND (T2.CustomerID=T1.CustomerID)
GROUP BY T1.CustomerID, T1.TransactionDate, T1.TransactionAmount, T1.TransactionID
ORDER BY T1.CustomerID, T1.TransactionDate DESC , T1.TransactionID DESC;

---

Ken Sheridan, Stafford, England

Answer 4

I use your method and your example downloaded and is successfully now and the speed significantly speed up.

Thanks so much!