Combining tables in an update query

[Avid Reader]

Hi all, I have a situation where I'm trying to combine data from a table and
have it update a field in another table.

Here's what I'm trying to do: In table 1 have a field called UPC# in column
a, ITEM in column b, DESC in column c. In table 2 I have the same fields
except for UPC#, what I need is to populate UPC# into table2.iglno where
the table2.iglno is null. There are over a thousand fields that need to be
populated into table 2. So basically what I'm trying to do is import table 1
UPC# field into table 2's field without overwriting any data that might be
in that field and not using any data from UPC# twice.

Please any help would be appreciated.

Thanks

 

[John Spencer (MVP)]

On a COPY of your tables try the following SQL.

UPDATE Table2 INNER JOIN Table1
ON Table2.Item = Table1.Item
SET Table2.IGLNo = Table1.[UPC#]
WHERE Table2.IGLNo Is Null

 

[John Vinson]

Hi all, I have a situation where I'm trying to combine data from a table and
have it update a field in another table.

Here's what I'm trying to do: In table 1 have a field called UPC# in column
a, ITEM in column b, DESC in column c. In table 2 I have the same fields
except for UPC#, what I need is to populate UPC# into table2.iglno where
the table2.iglno is null. There are over a thousand fields that need to be
populated into table 2. So basically what I'm trying to do is import table 1
UPC# field into table 2's field without overwriting any data that might be
in that field and not using any data from UPC# twice.

Please any help would be appreciated.

Thanks

ASSUMING - and I hope this is a correct assumption because it's going
to be very hard to do this otherwise - that ITEM is a primary key in
both tables and uniquely identifies which record's UPC goes into which
record's iglno, this is pretty easy.

Create a query joining Table1 to Table2 joining on Item. Change it to
an Update query. Put a criterion on Table1.iglno of

IS NULL

to exclude those rows where iglno already exists; and on the Update
To row put

[Table1].[UPC#]

Run the query by clicking the ! icon and you should get the desired
update.