Kayleigh
Member
- Local time
- Today, 08:08
- Joined
- Sep 24, 2020
- Messages
- 706
I am attempting to calculate a running total column on a union query of two tables - invoice and transactions so that I can design a statement which involves all this info.
Because there are two tables involved I can't use the ID as the unique field in the DSum calculation, instead I tried combining it with the date, however sometimes there are multiple transactions on the same day so this doesn't work either. I also tried with a timestamp but couldn't get that to work.
Can anyone suggest how I can have a running total of the (invoice due + credit) - debit so it is correct for every row.
This is the current calculation:
The relevant fields are:
- fldtransactionID (unique to transaction table)
- fldinvoiceID (unique to invoice table)
- cfDate (date with time from both tables)
- invdue (amount field from invoice table)
- fldtdebit (amount paid from transaction table)
- fldtcredit (other owed amount from transaction table)
A sample database can be made available.
Because there are two tables involved I can't use the ID as the unique field in the DSum calculation, instead I tried combining it with the date, however sometimes there are multiple transactions on the same day so this doesn't work either. I also tried with a timestamp but couldn't get that to work.
Can anyone suggest how I can have a running total of the (invoice due + credit) - debit so it is correct for every row.
This is the current calculation:
Code:
cfRunningBalance: CCur(DSum("0-nz(invdue,0)-Nz(fldtdebit,0)+Nz(fldtcredit,0)","qryparentAccfilter","cfDate <=" & Format([cfDate],"\#yyyy-mm-dd\#") & " AND (fldTransactionID <= " & [fldTransactionID] & " OR cfDate <> " & Format([cfDate],"\#yyyy-mm-dd\#") & ")"))
The relevant fields are:
- fldtransactionID (unique to transaction table)
- fldinvoiceID (unique to invoice table)
- cfDate (date with time from both tables)
- invdue (amount field from invoice table)
- fldtdebit (amount paid from transaction table)
- fldtcredit (other owed amount from transaction table)
A sample database can be made available.