Balance with Dsum but problem

[69latrickz]

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]

Hi. Welcome to AWF!

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

 

[69latrickz]

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] & "'")

 

[69latrickz]

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.

 

[theDBguy]

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.

 

[69latrickz]

Try taking out the single quotes, if your field is numeric.

I have nothing else to say but that you are the GOAT. Thank you so much.

 

[theDBguy]

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.