balance for invoice with multiple payments

[SLP]

Hi. I have an invoice form with subform for payments. Everything is working
fine except I need to have a balance show for each invoice. If there is only
one payment, no problem. My problem is with multiple payments. For example,
invoice is for 165. First payment is 160. Balance shows 5. Next payment is
3. My balance is showing 162 instead of 3. Suggestions appreciated.
Thanks.

 

[Allen Browne]

Create a query that groups the payments for the invoice.
Outer-join that query to your invoice one.

1. Create a query based on the Payments table.
Depress the Total button on the toolbar.
Group By the InvoiceID.
Sum the payment amount.
Save the query as (say) qryPayment.

2. Create a query using your invoice table, and invoice-detail table
(assuming that one invoice can have many line items in a related table.)
Again, depress the Total button, and Group By InvoiceID and sum the amount.
Save as (say) qryInvoice.

3. Create a query using qryInvoice and qryPayment as input 'tables.'
Double-click the line joining the 2 tables in the upper pane of query
design.
Access pops up a dialog showing 3 options.
Choose the one that says:
All records from qryInvoice, and any matches from qryPayment.

This 3rd query will give you the correct invoice total and payment total.

 

[Al Campagna]

SLP,
It would be helpful to see what calculation you have now... but...

Your calculation should be... given only one charge...
Balance = Charge - Sum(Payments)
Or even better... a similar example... like a checkbook.
Balance = Sum(Credits) - Sum(Debits)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."