Need Help in access 2010

[vaidehikumar]

I have two tables Invoice and Payment. I am running a query to get the Invoice balance. Invoice Balance=Invoice table.Invoice Amount-Payment Table.Amount received.
Some times the same invoice has multiple payments and if I want to show each time when an invoice gets paid, I want the query to show the balance, so second time when the invoice gets paid I need to get the balance from Invoice Balance-Amount received, instead of Invoice Amount-Amount Received. How to do this? . I can aggregate the Amount Received and then do the calculations, but, this way I am not able to show each payment date if an invoice has multiple payments. Please help.

 

[Pat Hartman]

This is something that is easily done with a report but not so easily in a query. It is also pretty inefficient in a query but quite efficient in a report. The difference is that with a query, there is no way to define intermediate groupings. All rows are either detail or summary and there is only a single level of summary available. Reports are sequential processes. They read their recordsource from front to back and can accumulate and break (present subtotals) at many levels. Queries are not presented to users. They see reports and forms so there is really no reason to create a query like this and finally, queries that include aggregation are not updateable so they can't be used as the RecordSource for a form where you want to update the data.

Given that, there are multiple ways of accomplishing this task, the most efficient is to create a query that sums the data to the beginning balance for each invoice. Then union that query with a query that selects the details for the period you want to show. The resulting recordset will not be updateable but it will show a beginning balance plus any period activity for each invoice.