Cumulative Sum with Duplicate Date

islam qasem 1 Reputation point
2021-04-16T02:55:49.997+00:00

Hi

i'm trying to calculate running sum for an account statement but i have problem when there is duplicate dates the sum is not changing until the date changes

this is the result :

88434-1.png

and this is the query :

SELECT Trans.TransDate, Trans.Cr, Trans.Dr, (SELECT SUM(t.[Dr]-t.[Cr]) FROM Trans t WHERE t.[TransDate]<= Trans.[TransDate] AND t.Account = Trans.Account) AS Balance
FROM Trans
WHERE (((Trans.Account)="Cash"))
ORDER BY Trans.TransDate;

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
849 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ken Sheridan 2,756 Reputation points
    2021-04-16T15:53:53.73+00:00

    You might like to take a look at Balances.zip in my public databases folder at:

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

    This little demo file includes examples which cater for duplicate transaction dates. To act as a tie breaker the table must include a TransactionID or similar column (usually an autonumber) as its primary key. The appropriate query in the demo is:

    SELECT T1.[CustomerID], T1.TransactionDate, T1.Credit, T1.Debit,
    SUM(T2.Credit-T2.Debit) AS Balance
    FROM TransactionsCD AS T1 INNER JOIN TransactionsCD AS T2
    ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
    AND (T2.TransactionDate<=T1.TransactionDate)
    GROUP BY T1.[CustomerID], T1.TransactionDate, T1.TransactionID, T1.Credit, T1.Debit
    ORDER BY T1.TransactionDate DESC , T1.TransactionID DESC;

    The above query is ordered by transaction date and ID order in descending order, but will work equally well in ascending date and ID order.

    0 comments