Use Update Query to Add Fields

[Kaykayme]

I am revising a make table query that used all the fields of a table
[Table1]. Since that table [Table1] never received updates I linked another
table [LinkedTable1] from another database that is regularly updated and used
this table [LinkedTable1] instead of the one already in the query [Table1].
The [LinkedTable1] does not have all of the fields of [Table1] and [Table1]
does not have all of the records of [LinkedTable1]. Since an Update query is
run to update the fields in [Table1] that are not in [LinkedTable1] anyway,
will running that same Update query add the missing fields in [LinkedTable1]
with the new updated data? If not, what will be the best way to do that?

 

[Michel Walsh]

Assuming you have a table of UnitCosts and wish to update it with NewCosts,
which may also incorporate NEW ITEMS that are not yet in UnitCosts. The
following query will do it (in Jet, not in MS SQL Server):


UPDATE newCosts LEFT JOIN unitCosts
ON newCosts.ItemID = unitCosts.ItemID
SET unitCosts.ItemID= newCosts.ItemID,
unitCosts.UnitPrice = newCosts.UnitPrice,
unitCosts.ItemDesc = newCosts.ItemDesc





and you continue for each fields which matter.


As usual, before making experimentation, ALWAYS have a backup, so you won't
have data lost.





Vanderghast, Access MVP