Update query?

[superrob5]

I have a table that has a person's last name.
Smith


Another table which has the First name then last name.
Jack Smith

is there a way to have an update query that will change smith in the first table to Jack Smith in the second table.

Thanks

[MAK]

any primary key - foreign key relationship between those tables?

If not then "will smith", "jack smith", "Tom Smith", "Another smith" will all become "Jack smith" on the table we are updating.

[superrob5]

yes beacsue there is no situation where tehy would double. I am going to use relationships after I fix the db.

[galileeian]

Oh you really should use something more unique to identify individuals. Never mind though. The following code should do the trick. I have called the tables table1 and table2 they each have a field called name. table1 has the full names ie Jack Smith table2 has only the surname.

the space after the wildcard char (*) ensures that a name such as james Smallsmith would not get replaced by Jack Smith

UPDATE Table1, Table2 SET Table2.name = table1.name
WHERE (((Table1.name) Like "* " & [table2].[name]));

Ian