Solved Cumulative Total in query (1 Viewer)

Krayna

Member
Local time
Today, 23:00
Joined
Sep 24, 2020
Messages
512
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: 58

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:00
Joined
May 7, 2009
Messages
14,555
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, 23:00
Joined
Sep 24, 2020
Messages
512
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
Tomorrow, 07:00
Joined
May 7, 2009
Messages
14,555
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, 23:00
Joined
Sep 24, 2020
Messages
512
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: 77

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:00
Joined
May 7, 2009
Messages
14,555
check query1 if it calculate correctly.
 

Attachments

  • TransactionType TEST.accdb
    3.1 MB · Views: 64

Krayna

Member
Local time
Today, 23:00
Joined
Sep 24, 2020
Messages
512
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
Tomorrow, 07:00
Joined
May 7, 2009
Messages
14,555
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: 73

Krayna

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

Krayna

Member
Local time
Today, 23:00
Joined
Sep 24, 2020
Messages
512
@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: 65

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:00
Joined
May 7, 2009
Messages
14,555
see the query now.
 

Attachments

  • TransactionTest v3.accdb
    3.2 MB · Views: 71

June7

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

Krayna

Member
Local time
Today, 23:00
Joined
Sep 24, 2020
Messages
512
@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
Tomorrow, 07:00
Joined
May 7, 2009
Messages
14,555
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, 23:00
Joined
Sep 24, 2020
Messages
512
Tried that but now it won't format any values in column to currency.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:00
Joined
May 7, 2009
Messages
14,555
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
Tomorrow, 07:00
Joined
May 7, 2009
Messages
14,555
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, 23:00
Joined
Sep 24, 2020
Messages
512
Your last solution worked best :)
Thanks for the suggestions
Have a great day!
 

Users who are viewing this thread

Top Bottom