Tricky Update Query based on containing records in another table

[hashmio]

Hi



I have two tables:

Table 1

Name	Origin
Smith John E
Blogs Tomasz B
Ahmad Sarah
Smith A
Bill Ahmad F

Table2

Name	Origin
John	English
Tomasz	Polish
Ahmad	Arab
Smith	English

I would like to update the Table1.Origin with Table2.Origin where there is a match on the Table2.name

I hope you can help

Thanks

Os

[June7]

Table1 should have the name parts in separate fields: FirstName, LastName

You want to assign gender based on a person's first name? My name is June (female) but I had an uncle named June. Another uncle named Merideth. My mother (Carbrey) was named after her father. I have a niece Kelly and nephew Shawn.


UPDATE Table1 SET Table1.Gender = Table2.Gender WHERE Left([Table1.Name], InStr([Table1.Name], " ")-1) = Table2.Name;

or

UPDATE Table1 SET Table1.Gender= DLookup("Gender", "Table2", "Table2.Name='" & Left([Table1.Name], InStr([Table1.Name], " ")-1) & "'")

[hashmio]

I updated the original post. I can't split the name as I have a very large pre-populated table where the complete name exists in 1 cell and sometimes has initials. Unless you can think of a way I can split it

[June7]

So the first name isn't even first in the string and this certainly complicates. Will the first name (or initial) always follow the lastname? Will there be any single word names, like Madonna, Cher, Fergie?

What gender is Smith A?

The names can and should be split into separate fields but would probably need VBA code to accomplish.

Regardless of the approach, any output would require thorough review.

[hashmio]

So the first name isn't even first in the string and this certainly complicates. Will the first name (or initial) always follow the lastname? Will there be any single word names, like Madonna, Cher, Fergie?

What gender is Smith A?

The names can and should be split into separate fields but would probably need VBA code to accomplish.

Regardless of the approach, any output would require thorough review.

Please ignore the gender example as I actually wanted to update the names origin instead. The name will always have the last name first followed by a firstname or initial or both

[June7]

Consider this expression for extracting the first name.

Left(Mid([Name],InStr([Name]," ")+1),InStr(Mid([Name],InStr([Name]," ")+1)," "))