January 15th, 2013, 11:06 AM
Many To One Update Issue
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:
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.
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
January 16th, 2013, 03:05 PM
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.
January 17th, 2013, 08:13 AM
Not sure I understand. Can you give example query? Thanks