#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0

    Many To One Update Issue


    Hello:

    My problem is this. I am working on Accounts Receivable invoices/payments. In one Table Inv I store invoice invoices as invno, amount,balance,etc. The table is keyed by invoice number. In another table Pay I store payments,credits,adjustments. This table is also keyed by invoice number but allows duplicates since there be multiple entries in the Pay table for any given invoice. When printing aging reports and statements I don't want to print invoices that have a zero balance. Before printing I set the balance field in the Inv table to the invoice amout. Then I want to adjust the balance field in the Inv table based on payments in the Pay table. I have tried the following:

    UPDATE inv
    LEFT JOIN pay
    ON inv.invno = pay.invno
    SET inv.balance = inv.balance - pay.amount

    This works if there is only 1 payment in the pay file. If more than one it only applies the 1st.

    I tried:

    UPDATE inv
    SET balance = amount - (SELECT SUM(amount) FROM pay
    WHERE inv.invno = pay.invno)

    This sort of works. If the pay table has a matching invoice number all works as planned. However, if the pay table does not have a matching invoice number (no payments have been made) the balance field is set to NULL. This won't work because the balance field should not be adjusted.

    Thanks in advance for any assistance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    this could be solved by adjusting the query that returns the results for printing. just have it take the invoice total minus the total payments made resulting in remaining balance to print.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0
    Hello:

    Not sure I understand. Can you give example query? Thanks

IMN logo majestic logo threadwatch logo seochat tools logo