Solved Cumulative Total in query

[Kayleigh]

Hi I have a table which records transactions related to a property.
Currently the query to display this has a balance to display the cumulative balance but this includes all transaction types. I would like to calculate the total income cumulatively which = Rent In (TransactionID 2) - Management (TransactionID 14) but I could not work out how to do
the fields to calculate are credit (for rent in) and debit (for managment).
This is the query which works to calculate total cumulative balance:

SELECT qryPropAccFilter.*, DSum("Nz(fldCredit,0)-Nz(fldDebit,0)","qrypropAccFilter","fldDate  <=" & Format([fldDate],"\#yyyy-mm-dd\#") & " AND (fldTransactionID <= " & [fldTransactionID] & "  OR fldDate <> " & Format([fldDate],"\#yyyy-mm-dd\#") & ")") AS cfBalance
FROM qryPropAccFilter;

Also attached a copy of the transaction table so you can see fields.
Would appreciate any pointers here.

 

[arnelgp]

not tested

SELECT qryPropAccFilter.*, IIF([fldTransactionID = 2 Or fldTransactionID=14,
DSum("Nz(fldCredit,0),"qrypropAccFilter","fldDate <=" & Format([fldDate],"\#yyyy-mm-dd\#") & " AND (fldTransactionID <= " & [fldTransactionID] & " And ID = 2") -
DSum("Nz(fldDebit,0),"qrypropAccFilter","fldDate <=" & Format([fldDate],"\#yyyy-mm-dd\#") & " AND (fldTransactionID <= " & [fldTransactionID] & " And ID = 14"), Null) As Balance
FROM qryPropAccFilter;

 

[Kayleigh]

It comes up with syntax errors.
Also note that what you have done not completely accurate - the transactionTypeID must be 2 or 14 but the transactionID can be anything

 

[arnelgp]

it is obvious is used ID instead of transactionTypeID, but still not tested
since i don't have your data or query.

 

[Kayleigh]

Here is a copy of my DB with anonymised data. You must open up to a property for the queries to actually work.

 

[arnelgp]

check query1 if it calculate correctly.

 

[Kayleigh]

That's great. It only shows up in rows of typeID 14. Can it work for all rows?

 

[arnelgp]

see again query1. note that your TransactionID does not make any sense to me.
you have Sept transaction with high transactionID while on Oct there is a Low
transactionID when this is autonumber field?

 

[Kayleigh]

Thank you arnold
Yes its a test DB - the data is totally fictional.

 

[Kayleigh]

@arnelgp I need your help again with this please.
The numbers are not tallying for the income column and can't figure out why!
I have attached same DB. Query you supplied is named: qryPropAccInc. If you open on the first property, it appears they are in debt -6855! Not sure how this happened??

 

[arnelgp]

see the query now.

 

[June7]

An alternative is a report using textbox RunningSum property.

 

[Kayleigh]

@arnelgp it works great. Can you explain your approach please?

Also is there any way to have a blank result when no total income - at moment it shows a £0 but its misleading. I attempted several methods but it would either show Error or would not format column as currency.

 

[arnelgp]

if you can view the function in a Module1.
at the near end of the function, change this to:

..
    If IsNumeric(Value) Then
        Value = CDbl(Value)
        If Value < 1 Then Value = Null
    End If

so it will return Null instead of 0.

 

[Kayleigh]

Tried that but now it won't format any values in column to currency.

 

[arnelgp]

use a form/datasheet and format it there.
or if you are referring it from a textbox, format the textbox.

or you can modify the function to return a Double instead of variant.

 

[arnelgp]

you can also edit the Query, and on that "Expression", add the format:

cfTotalIncome: Format(getrunningbalance([fldTransactionID]),"£#,##0.00;-£#,##0.00")

 

[Kayleigh]

Your last solution worked best
Thanks for the suggestions
Have a great day!