balance for invoice with multiple payments

Discussion in 'Microsoft Access Form Coding' started by SLP, Sep 11, 2008.

  1. SLP

    SLP Guest

    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.
     
    SLP, Sep 11, 2008
    #1
    1. Advertisements

  2. SLP

    Allen Browne Guest

    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" <> wrote in message
    news:...
    > 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, Sep 11, 2008
    #2
    1. Advertisements

  3. SLP

    Al Campagna Guest

    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" <> wrote in message
    news:...
    > 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.
     
    Al Campagna, Sep 11, 2008
    #3
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michelle

    New Balance from Previous Balance

    Michelle, May 6, 2004, in forum: Microsoft Access Form Coding
    Replies:
    5
    Views:
    428
    Jonathan Parminter
    May 10, 2004
  2. Guest

    Invoice Payments subForm - help with running balance

    Guest, Jun 6, 2005, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    259
    Guest
    Jun 6, 2005
  3. Guest

    Invoice Payments Subform / Reset Balance Due?

    Guest, Aug 10, 2005, in forum: Microsoft Access Form Coding
    Replies:
    6
    Views:
    238
    Joshua A. Booker
    Aug 11, 2005
  4. Vensia

    Display Invoice Balance

    Vensia, Sep 6, 2005, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    136
    Vensia
    Sep 6, 2005
  5. Vensia

    Display Invoice Balance

    Vensia, Sep 7, 2005, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    152
    Vensia
    Sep 7, 2005
Loading...

Share This Page