Update and Append - I can't think!!!

[WB786]

I have two tables and need to update table A with table B values joined by ProdID. But also need to append any records from table B where ProdID didn't equal table A ProdID during the update process.

I am sure it is possible I think my head is ready to go home and can't think anymore. This is for Access 2003.

Thanks,

WB

 

[PHV]

Something like this ?
UPDATE tableA RIGHT JOIN tableB ON tableA.ProdID = tableB.ProdID
SET tableA.ProdID = tableB.ProdID
, tableA.someField = tableB.someField
, tableA.anotherField = tableB.anotherField;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WB786]

This will only update table A but not append records from table B where there is no matching record in table A. Correct?

Thanks,

WB

 

[PHV]

Have you tried it ?
It should also append records from table B where there is no matching record in table A.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886