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

  • 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!

    Thursday, November 12, 2015 2:26 AM

Answers

  • 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

    • Marked as answer by yaukaryi Monday, November 16, 2015 8:42 AM
    Saturday, November 14, 2015 10:20 PM

All replies

  • 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


    Thursday, November 12, 2015 2:29 AM
  • 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 :)

    Thursday, November 12, 2015 3:37 AM
  • 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

    • Marked as answer by yaukaryi Monday, November 16, 2015 8:42 AM
    Saturday, November 14, 2015 10:20 PM
  • I use your method and your example downloaded and is successfully now and the speed significantly speed up.

    Thanks so much!

    Monday, November 16, 2015 8:43 AM