Solved Filter query for running totals (1 Viewer)

angekap

New member
Local time
Today, 11:41
Joined
Mar 16, 2021
Messages
12
Hi, I was wondering if anyone can help me with filtering out a query called TRANSSUP which has a list of suppliers and I have a credit and debit column and have used DSUM for running totals with a balance column. The fields in the query are: ID,DATE,SUPPLIER,REC_NO,DEBIT,CREDIT which are derived from the TRANSACTIONS Table and then I have created in query design the next there columns as:

DEBRUNSUM: DSum("DEBIT","TRANSACTIONS","ID<=" & [ID])

CRRUNSUM: DSum("CREDIT","TRANSACTIONS","ID<=" & [ID])

BALANCE: [CRRUNSUM]-[DEBRUNSUM]

It all works fine as the BALANCE field works great in running totals, but how do you select a SUPPLIER and have the CREDIT,DEBIT and BALANCE fields work only for the selection. I was thinking of using a Form with a ComboBox to select the supplier and the subform based on the query to produce the results.

The SQL is as follows for the query:

SELECT TRANSACTIONS.ID, TRANSACTIONS.Date, TRANSACTIONS.SUPPLIER, TRANSACTIONS.REC_NO, TRANSACTIONS.CREDIT, TRANSACTIONS.DEBIT, DSum("DEBIT","TRANSACTIONS","ID<=" & [ID]) AS DEBRUNSUM, DSum("CREDIT","TRANSACTIONS","ID<=" & [ID]) AS CRRUNSUM, [CRRUNSUM]-[DEBRUNSUM] AS BALANCE
FROM TRANSACTIONS;

That would be great if anyone can guide me

Thanks
Ange
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:41
Joined
Oct 29, 2018
Messages
18,786
Hi. Welcome to AWF!

However you decide to implement it, you will basically just need to add to your DSum() criteria to include the SUPPLIER.

For example:
Code:
DEBRUNSUM: DSum("DEBIT","TRANSACTIONS","ID<=" & [ID] & " AND SUPPLIER='" & [SUPPLIER] & "'")
Hope that helps...
 

angekap

New member
Local time
Today, 11:41
Joined
Mar 16, 2021
Messages
12
Thank You So Much It worked !!!
 

Users who are viewing this thread

Top Bottom