Solved Cumulative Total in query (1 Viewer)

Krayna

Member
Local time
Today, 09:36
Joined
Sep 24, 2020
Messages
603
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: 244

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:36
Joined
May 7, 2009
Messages
16,105
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;
 

Krayna

Member
Local time
Today, 09:36
Joined
Sep 24, 2020
Messages
603
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, 16:36
Joined
May 7, 2009
Messages
16,105
it is obvious is used ID instead of transactionTypeID, but still not tested
since i don't have your data or query.
 

Krayna

Member
Local time
Today, 09:36
Joined
Sep 24, 2020
Messages
603
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: 320

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:36
Joined
May 7, 2009
Messages
16,105
check query1 if it calculate correctly.
 

Attachments

  • TransactionType TEST.accdb
    3.1 MB · Views: 262

Krayna

Member
Local time
Today, 09:36
Joined
Sep 24, 2020
Messages
603
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, 16:36
Joined
May 7, 2009
Messages
16,105
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: 287

Krayna

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

Krayna

Member
Local time
Today, 09:36
Joined
Sep 24, 2020
Messages
603
@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: 300

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:36
Joined
May 7, 2009
Messages
16,105
see the query now.
 

Attachments

  • TransactionTest v3.accdb
    3.2 MB · Views: 321

June7

AWF VIP
Local time
Today, 00:36
Joined
Mar 9, 2014
Messages
4,307
An alternative is a report using textbox RunningSum property.
 
Last edited:

Krayna

Member
Local time
Today, 09:36
Joined
Sep 24, 2020
Messages
603
@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, 16:36
Joined
May 7, 2009
Messages
16,105
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.
 

Krayna

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:36
Joined
May 7, 2009
Messages
16,105
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, 16:36
Joined
May 7, 2009
Messages
16,105
you can also edit the Query, and on that "Expression", add the format:

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

Krayna

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

Users who are viewing this thread

Top Bottom