can i use sum function along with union operation?

H

hello

i want to add data in two tables and put result in a third table, field names
are same in all three tables. union operation can only bring data from two
different tables but could not add.
 
M

Michel Walsh

UPDATE (table3 LEFT JOIN table 2 ON table3.id=table2.id)
LEFT JOIN table1 ON table3.id = table1.id
SET table3.qty = Nz(table2.qty, 0) + Nz(table1.qty, 0 )


would set the qty values as the sum of those in table1 and in table2, for
the same 'id'. If an id does not exist in table3, no record in table3 will
be created; if an id exists in only one of the two tables { table1,
table2 }, that quantity will be used.


Hoping it may help,
Vanderghast, Access MVP
 
Top