update a column in one table with the value of another column in another table

[johnmerlino]

Hey all, I would like to update one column with the value of another column from another table:

table_1
names_1 addresses
BLAIR,SHERON S 6110 Browen Road
BROWN,BRENON I H/E BROWN,WANDA R 1100 Lake Road
RODRIGUEZ,LILLIAN M 3332 Brick Road
GLAUBMAN,ALAN & SHORSTEIN,LILLIAN 3332 Brick Road


table_2
names
BLAIR,SHERON S
BROWN,WANDA R
RODRIGUEZ,LILLIAN M
GLAUBMAN,ALAN


Notice how table_1 contains the same names as table 2 but table 2 also has additional content that's not needed. So basically where the names are like each other, I would like to assign table 1 content into table 2.

So I would like it to look like this:


table_2
BLAIR,SHERON S 6110 Browen Road
BROWN,WANDA R 1100 Lake Road
RODRIGUEZ,LILLIAN M 3332 Brick Road
GLAUBMAN,ALAN 3332 Brick Road

You think something like this would do it?
UPDATE table_1
SET table_1.names_1 = table_2.names
WHERE (((InStr([table_1].[names_1],[table_2].[names]))>0));
;
Note that no two fields are exaclty alike so I don't think I can do inner join here.

Thanks for response

 

[apr pillai]

Isn't it possible to create a separate field for Name alone in both tables and move the Name alone into that field with the use of Instr() and Left() functions like LEFT(name_1,INSTR(1,name_1,",")-1). You can run UPDATE to prepare both table this way.

The new name field in both tables can be linked to update data from one table to the other. One of the table's name field must be defined as PrimaryKey.

 

[Rabbie]

In a relational database like Access it is not good practice to store the same information in different places. This goes against the rules of data normalization. Basicaly you should store the name in one field and the address in another field. You can always concatenate them when you need both.