Unanswered: Group similar records in a query for a total
Hope I explained this right in my subject......Access 2007
I've built a database of customers and invoices. I need to put together a query for a report on open invoices to create aging statements. So I've joined Customer and Invoices in Qry1. Next is to take out paid invoices in Qry2 that gives back all invoices that are not matched to the paid table.
Where I run into trouble is on partial payments that have multiple payments and balances due
So there is a Qry3 for invoices that are on the paid table. I built an expression BAL subtracting payment from invoice amount. If a single partial payment is on them, and there is a balance due - my BAL returns a number and is set to criteria >0.
The problem I need to get around is I have some invoices that have multiple payments on them. My Qry3 BAL will only deduct a payment at a time from the invoice amount, so I don't have a true balance and if a invoice is paid in full after multiple payments I'm not getting the 0 BAL to eliminate it from Qry3.
I played around for some time bringing all the payments into the report to try grouping payments there, but was unable to eliminate paid in fulls, or calculate a balance from total payments. I would like to work this in the query.
Is there a way to use Max or last in the query? Or a where cause on the invoice number? expression Sum([PAYMENT]) Where [INVNO] = subqry.[INVNO]?
I can't get my head around it? Not sure there's a way.
So the 2 things I would like in Qry3 = group together all [PAYMENT] by [INVNO], get a total of those payments, subtract that total from [INVAMT] for BAL, then keep it if BAL >0
Possible, yes. Anything is possible. Accomplishing this in one query is possible, but more complicated and harder to debug.
I am not entirely clear on your situation, but I suggest walking towards it one step at a time using multiple queries.
Use a query to group by invoice and sum payments. This will provide a total paid for each invoice.
Use a second query to compare the first query against the original invoices. This way you know the sum was done correctly because it happens in the other query first. In this one, put your balance expression, which will be original invoice minus any payments.