Hi Guys,
Still self-teaching....
I have two tables, one called invoices and one called trans
The invoices table among others has the fields 'invno' and 'net'
The trans table has 'ipay' and 'invno'
What I want to do is make the 'net' value of each line in the invoices table equal the sum of the ipay fields in the trans table for each relevant invno
What I have so far -
update invoices
set invoices.net = (select sum(trans.ipay)
from trans join invoices on trans.invno=invoices.invno
where invoices.invno = trans.invno)
The problem is that every invoice ends up with the total of the ipay from the entire trans table.
Hope this makes sense, any help greatly appreciated.
Matt