Help searching one column against another

[ScottW]

I am new here. I need a little direction on the right path to take. My background is RPG programming so I am very shakey when it comes to SQL and Access DB.

I have two spreadsheets I am importing into a Access DB. These two spreadsheets have lists of first and last names.

I need to treat one as the "master" listing of names and take the other table and search against the master looking for occurences of the names. To start out, I will just try to match last name against last name. The only catch is the first and last names are all together in one column in each table.

So, what I think I am looking for is a way to take a character string and search another table column for the same character string.

For example:
Let's say the "master" table has a column for name and one of the rows is Duck, Donald. The second table has a name in a row Duck, Buford. I would want to take the string of "Duck" and search the master table for "Duck."

I guess I could do this because the first and last names are seperated by a comma. The reason for not searching for the entire first and last name is because most of the time they do not match exactly. There may be a middle initial or maybe the first name was mispelled. Anyway, I am looking to strip out the last name and search for it in another table.

Where would you suggest I start looking?

Thank you.
Scott

[FoFa]

I would create two new fields in each table lest call them lastName and FirstName. Then parse the name field into those.
UPDATE MyTable
SET [LastName] = LEFT([FullName],instr([FullName],',')-1)

and

UPDATE MyTable
SET [FirstName] = RIGHT([FullName],LEN([FullName]) - instr([FullName],','))

Basically, may need to adjust slightly
Then you can match on different columns instead.

[ScottW]

Originally Posted by FoFa

I would create two new fields in each table lest call them lastName and FirstName. Then parse the name field into those.
UPDATE MyTable
SET [LastName] = LEFT([FullName],instr([FullName],',')-1)

and

UPDATE MyTable
SET [FirstName] = RIGHT([FullName],LEN([FullName]) - instr([FullName],','))

Basically, may need to adjust slightly
Then you can match on different columns instead.

Thank you FoFa. This heads me in the right direction. I am kind of going in 100 diffent directions and this truly helps me. Thank you again!!!
Scott

[ScottW]

Originally Posted by FoFa

I would create two new fields in each table lest call them lastName and FirstName. Then parse the name field into those.
UPDATE MyTable
SET [LastName] = LEFT([FullName],instr([FullName],',')-1)

and

UPDATE MyTable
SET [FirstName] = RIGHT([FullName],LEN([FullName]) - instr([FullName],','))

Basically, may need to adjust slightly
Then you can match on different columns instead.

I am stuck. Where do I put in the "update" statements above? I tried the SQL view but it doesn't pull the data from the column as I expected. Instead it is prompting me for a value.

What could I be missing?

Thank you.

[ScottW]

Never mind. I finally figured it out. I am stumbing though this. I didn't know I just needed to execute the query to get the table updated. It is working absolutely perfectly.......

THANK YOU.