Filter by one "Many" value in a query
Posted on 2004-10-07
Ok, this is been a serious PITA for me - I've got an Access 2000 database for a car dealership, and I'm having trouble taking values that I calculate in a form and instead calculate them in a query.
For example, we have a payment table to track customers' monthly payments. It is setup with a primary key for each payment, and the invoice number from the buys table is associated with it.
paymentID InvoiceNumber PaymentAmount PaymentDate Receipt Number
1 123 400.00 9/21/04 239487
2 143 142.78 8/30/04 059201
3 123 400.00 10/04/04 239498
4 102 350.00 7/23/04 098520
I have a query take these values plus all the sales info included by the invoice number such as vehicle and customer info (date sold, monthly payment, total financed, etc), then a form that filters this by invoice number with a combo box.
Based on what is loaded into the form from the query, the payments are summed up and past due, months past due, # of payments made, # of payments left, interest-based calculation for early payoff etc etc are done.
Ok! so I want to do a sort of "For/Next" loop like "For each distinct Invoice Number, sum up Payment value and store that sum". How do I set a filter for another query to do that?
Invoice Number LastPaymentMade PaymentSum PastDueAmount
102 7/23/04 350.00 700.00
123 10/04/04 800.00 400.00
143 8/30/04 142.78 300.78