Solved Cumulative Total in query (1 Viewer)

Krayna

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:15
Joined
May 7, 2009
Messages
15,037
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, 15:15
Joined
Sep 24, 2020
Messages
562
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:15
Joined
May 7, 2009
Messages
15,037
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, 15:15
Joined
Sep 24, 2020
Messages
562
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: 153

arnelgp

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

Attachments

  • TransactionType TEST.accdb
    3.1 MB · Views: 125

Krayna

Member
Local time
Today, 15:15
Joined
Sep 24, 2020
Messages
562
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:15
Joined
May 7, 2009
Messages
15,037
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: 133

Krayna

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

Krayna

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

arnelgp

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

Attachments

  • TransactionTest v3.accdb
    3.2 MB · Views: 140

June7

AWF VIP
Local time
Today, 06:15
Joined
Mar 9, 2014
Messages
3,882
An alternative is a report using textbox RunningSum property.
 
Last edited:

Krayna

Member
Local time
Today, 15:15
Joined
Sep 24, 2020
Messages
562
@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:15
Joined
May 7, 2009
Messages
15,037
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, 15:15
Joined
Sep 24, 2020
Messages
562
Tried that but now it won't format any values in column to currency.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:15
Joined
May 7, 2009
Messages
15,037
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:15
Joined
May 7, 2009
Messages
15,037
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, 15:15
Joined
Sep 24, 2020
Messages
562
Your last solution worked best :)
Thanks for the suggestions
Have a great day!
 

Users who are viewing this thread

Top Bottom