Table: Transactionstbl
Fields:
TransID - Autonumber
CustomerID - Number
Amount - Currency
I made a query to display a running balance for the field Amount and named it as Balance
Balance: DSum(“[Amount]”, “[Transactionstbl]”, “[TransID]<=“ & [TransID])
The data somehow looks like this:
TransID CustomerID Amount Balance
1 3 20 20
2 1 50 70
3 3 10 80
4 2 10 90
As you can see, the running balance worked but I would like it to compute the balance per CustomerID, not all the transactions. I tried changing the query to
Balance: DSum(“[Amount]”, “[Transactionstbl]”, “[CustomerID]=“ & [CustomerID]) but the data looked like this:
TransID CustomerID Amount Balance
1 3 20 30
2 1 50 50
3 3 10 30
4 2 10 10
My problem here is that the running balance didn’t work anymore. I’d like to display a running balance per customer like:
TransID CustomerID Amount Balance
1 3 20 20
2 1 50 50
3 3 10 30
4 2 10 10
Does anyone perhaps know how I can somehow manage to make it work? I read that multiple DSUM criteria would do but I get an error when I use Balance: DSum(“[Amount]”, “[Transactionstbl]”, “[TransID]<=‘“ & [TransID] & “‘ AND “[CustomerID]=“ & [CustomerID])