question

islamqasem-6084 avatar image
0 Votes"
islamqasem-6084 asked KenSheridan-7466 edited

Cumulative Sum with Duplicate Date

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;


office-access-dev
1.png (17.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered KenSheridan-7466 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.