Solved Cumulative Total in query (1 Viewer)

Kayleigh

Member
Local time
Today, 15:45
Joined
Sep 24, 2020
Messages
706
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:
SQL:
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.
 

Attachments

  • transaction.png
    transaction.png
    18.6 KB · Views: 384

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
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

Member
Local time
Today, 15:45
Joined
Sep 24, 2020
Messages
706
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
it is obvious is used ID instead of transactionTypeID, but still not tested
since i don't have your data or query.
 

Kayleigh

Member
Local time
Today, 15:45
Joined
Sep 24, 2020
Messages
706
Here is a copy of my DB with anonymised data. You must open up to a property for the queries to actually work.
 

Attachments

  • TransactionType TEST.accdb
    3.1 MB · Views: 481

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
check query1 if it calculate correctly.
 

Attachments

  • TransactionType TEST.accdb
    3.1 MB · Views: 428

Kayleigh

Member
Local time
Today, 15:45
Joined
Sep 24, 2020
Messages
706
That's great. It only shows up in rows of typeID 14. Can it work for all rows?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
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?
 

Attachments

  • TransactionType TEST.accdb
    3.1 MB · Views: 455

Kayleigh

Member
Local time
Today, 15:45
Joined
Sep 24, 2020
Messages
706
Thank you arnold
Yes its a test DB - the data is totally fictional.
 

Kayleigh

Member
Local time
Today, 15:45
Joined
Sep 24, 2020
Messages
706
@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??
 

Attachments

  • TransactionTest v3.accdb
    2.6 MB · Views: 459

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
see the query now.
 

Attachments

  • TransactionTest v3.accdb
    3.2 MB · Views: 515

June7

AWF VIP
Local time
Today, 07:45
Joined
Mar 9, 2014
Messages
5,423
An alternative is a report using textbox RunningSum property.
 
Last edited:

Kayleigh

Member
Local time
Today, 15:45
Joined
Sep 24, 2020
Messages
706
@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

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
if you can view the function in a Module1.
at the near end of the function, change this to:
Code:
..
    If IsNumeric(Value) Then
        Value = CDbl(Value)
        If Value < 1 Then Value = Null
    End If
so it will return Null instead of 0.
 

Kayleigh

Member
Local time
Today, 15:45
Joined
Sep 24, 2020
Messages
706
Tried that but now it won't format any values in column to currency.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
you can also edit the Query, and on that "Expression", add the format:

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

Kayleigh

Member
Local time
Today, 15:45
Joined
Sep 24, 2020
Messages
706
Your last solution worked best :)
Thanks for the suggestions
Have a great day!
 

Users who are viewing this thread

Top Bottom