Value from one table to another

[Rolf Rosenquist]

I have two tables, tblA and tblB.
tblB has only 2 fields; Number and BarCode.
tblA has the same and also others with data, but here the field BarCode is
empty.

Now I want to update tblA with the BarCode values from tblB where the both
Number fields are the same.
I thought it was simple, but I get stuck.

Any suggestions will be most appreciated.
/ Rolf

 

[John Vinson]

I have two tables, tblA and tblB.
tblB has only 2 fields; Number and BarCode.
tblA has the same and also others with data, but here the field BarCode is
empty.

Now I want to update tblA with the BarCode values from tblB where the both
Number fields are the same.
I thought it was simple, but I get stuck.

Any suggestions will be most appreciated.
/ Rolf

Create a Query joining the two tables.
Change it to an Update query with the query-type icon; on the UpdateTo
line under tblA.BarCode type

[tblB].[BarCode]

The brackets are required (otherwise it will try to update it to the
text string "tblB.BarCode"!)

Note that you must have a unique index on the Number field; you should
also consider renaming the field Number as it's a reserved word, and
you should be aware that the BarCode field should exist in one table
or the other BUT NOT BOTH - storing data redundantly is never a good
idea!

John W. Vinson[MVP]

 

[Rolf Rosenquist]

Many thanks!
It seems so easy, when you know it. And I have been struggling most of the
whole day with this!

I don't have uniqe index numbers as you suggest. I am working with the
customers data from an old system. But it seems like I don't get an update
on those records where there are duplicates on the numbers (actually I use
another name that is not reserved). But I guess we have to live with that
and update them manually afterwards.

/ Rolf

I have two tables, tblA and tblB.
tblB has only 2 fields; Number and BarCode.
tblA has the same and also others with data, but here the field BarCode is
empty.

Now I want to update tblA with the BarCode values from tblB where the both
Number fields are the same.
I thought it was simple, but I get stuck.

Any suggestions will be most appreciated.
/ Rolf

Create a Query joining the two tables.
Change it to an Update query with the query-type icon; on the UpdateTo
line under tblA.BarCode type

[tblB].[BarCode]

The brackets are required (otherwise it will try to update it to the
text string "tblB.BarCode"!)

Note that you must have a unique index on the Number field; you should
also consider renaming the field Number as it's a reserved word, and
you should be aware that the BarCode field should exist in one table
or the other BUT NOT BOTH - storing data redundantly is never a good
idea!

John W. Vinson[MVP]

 

[John Vinson]

I don't have uniqe index numbers as you suggest. I am working with the
customers data from an old system. But it seems like I don't get an update
on those records where there are duplicates on the numbers (actually I use
another name that is not reserved). But I guess we have to live with that
and update them manually afterwards.

It may - I'd actually say *would* - be worth investing some time to
properly normalize your tables, which includes assigning a valid
Primary key field and redesigning the tables so you don't store
duplicate data!

Glad to have been of help.

John W. Vinson[MVP]