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
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