PC Review


Reply
Thread Tools Rate Thread

balance for invoice with multiple payments

 
 
SLP
Guest
Posts: n/a
 
      11th Sep 2008
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.
 
Reply With Quote
 
 
 
 
Allen Browne
Guest
Posts: n/a
 
      11th Sep 2008
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SLP" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.


 
Reply With Quote
 
 
 
 
Al Campagna
Guest
Posts: n/a
 
      11th Sep 2008
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."

"SLP" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Invoice Amt. - Payments = Balance prodeji Microsoft Access Queries 2 20th Jul 2006 08:45 PM
Simple interest, multiple payments, loan balance =?Utf-8?B?TmluYXNOdW1iZXI=?= Microsoft Excel Worksheet Functions 1 9th Jan 2006 08:31 PM
Invoice Payments Subform / Reset Balance Due? =?Utf-8?B?QnJvb2s=?= Microsoft Access Form Coding 6 11th Aug 2005 01:38 AM
Invoice Payments subForm - help with running balance =?Utf-8?B?QnJvb2s=?= Microsoft Access Form Coding 0 6th Jun 2005 07:22 PM
CALCULATING PAYMENTS AND TOTAL OF PAYMENTS MARK Microsoft Excel Misc 2 12th Nov 2003 07:36 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:04 PM.