Balance with Dsum but problem (1 Viewer)

69latrickz

New member
Local time
Today, 12:22
Joined
Apr 6, 2021
Messages
4
Hi, I am looking for help to solve a query problem. I have a table called OPERATIONS with the following fields : N_Operation (which is the ID of the transaction), N_de_compte (which is the ID of the account that initiated the transaction), Credit, Debit. So every user can make multiples transactions. wanted to have running total so I wrote the following SQL :

SELECT OPERATIONS.N_Operation, OPERATIONS.N_de_compte, OPERATIONS.Montant_credit, OPERATIONS.Montant_debit, DSum(" [Montant_debit]","OPERATIONS","N_Operation<=" & [N_Operation]) AS DEB, DSum(" [Montant_credit]","OPERATIONS","N_Operation<=" & [N_Operation]) AS CRED, [CRED]-[DEB] AS BALANCE
FROM OPERATIONS;

It works perfectly, but I can't find a way to make it calculate the balances for each account and not for all the accounts together.

Thank you in advance for your help.
(Sorry for my english)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:22
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

Try adding a criteria for the account in your DSum() as well.
 

69latrickz

New member
Local time
Today, 12:22
Joined
Apr 6, 2021
Messages
4
Hi. Welcome to AWF!

Try adding a criteria for the account in your DSum() as well.
Hi, thank you for your reply.
In fact I came across a somewhat similar thread where you had responded with this solution:
DEBRUNSUM: DSum("DEBIT", "TRANSACTIONS", "ID<=" & [ID] & " AND SUPPLIER='" & [SUPPLIER] & "'")
 
Last edited:

69latrickz

New member
Local time
Today, 12:22
Joined
Apr 6, 2021
Messages
4
I applied it to my case like this:

DSum(" [Montant_debit]","OPERATIONS","N_Operation<=" & [N_Operation]& " AND N_de_compte='" & [N_de_compte] & "'") AS DEB, DSum(" [Montant_credit]","OPERATIONS","N_Operation<=" & [N_Operation]& " AND N_de_compte='" & [N_de_compte] & "'") AS CRED, [CRED]-[DEB] AS BALANCE

But when I execute the query, it shows "error" in the CRED, DEB and BALANCE columns.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 04:22
Joined
Oct 29, 2018
Messages
21,357
I applied it to my case like this:
...
But when I execute the query, it shows "error" in the CRED, DEB and BALANCE columns.
Try taking out the single quotes, if your field is numeric.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:22
Joined
Oct 29, 2018
Messages
21,357
I have nothing else to say but that you are the GOAT. Thank you so much.
Hi. You're very welcome. Although, you did all the work! Good luck with your project.
 

Users who are viewing this thread

Top Bottom