balance for invoice with multiple payments

S

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

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top