Solved Running Totals (1 Viewer)

[Kayleigh]

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:

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.

 

[XPS35]

Running totals are easy to use in reports and froms. Make a form or report with the union query.

 

[Pat Hartman]

The control has a RunningSum property and you set it to "Over All" or "Over Group".

When you are calculating a running sum in a query, each row MUST have a uniqueID because order is arbitrary. However, when doing this in a report, that isn't necessary. You just have to have the report sorted correctly using the Report's Sorting and Grouping options NOT the query's Order By clause and it is a sequential process because the production of a report is a sequential process. One record always "follows" another and therefore the report engine can easily keep the running sum.

 

[Kayleigh]

I understand however I would like to have this data as a query since it will be presented as such.

 

[theDBguy]

A sample database can be made available.

Probably a good idea to go ahead and do that.

 

[Kayleigh]

Thank you. I think I have resolved this by adding a unique date field using the advice here.