Flipping naming conventions to match two tables

[Guitarzann]

I have Table #1 that has the name field shown as "First Last". Table #2 has
been randomly keyed in by someone with the names as: "First Last", and others
"Last First" and yet others that are: "Last, Middle, First."

I attempted to try the instructions from a post here labeled "Fuzzy search."
However, the MID function does not work due to the random naming conventions
keyed in Table 2.

Is there another way I can program a query to conform Table 2 so that it
matches the consistent naming conventions of Table #1?

 

[Gina Whipp]

Guitarzann,

I am judging by your field set up that you are storing the FirstName and
LastName in the same field? Then I would have to say, nope... someone is
going to have put eyeball on each record. Another reason to have one field
for FirstName and another for LastName. Howver, another point/thought comes
to mind... why are you redundantly storing FirstName/LastName in mulitple
tables. You should only be storing the individuals unique ID in the second
table then you wouldn't care if they messed up the name entry because you'd
be using the unique ID to get the name.

Oh, and just as a side note, naming conventions means something totally
different in Access... so your subject line is a little misleading
(http://www.regina-whipp.com/index_files/NamingConventions.htm)

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I have Table #1 that has the name field shown as "First Last". Table #2 has
been randomly keyed in by someone with the names as: "First Last", and
others
"Last First" and yet others that are: "Last, Middle, First."

I attempted to try the instructions from a post here labeled "Fuzzy search."
However, the MID function does not work due to the random naming conventions
keyed in Table 2.

Is there another way I can program a query to conform Table 2 so that it
matches the consistent naming conventions of Table #1?

 

[John W. Vinson]

I have Table #1 that has the name field shown as "First Last". Table #2 has
been randomly keyed in by someone with the names as: "First Last", and others
"Last First" and yet others that are: "Last, Middle, First."

I attempted to try the instructions from a post here labeled "Fuzzy search."
However, the MID function does not work due to the random naming conventions
keyed in Table 2.

Is there another way I can program a query to conform Table 2 so that it
matches the consistent naming conventions of Table #1?

No, not even in principle.

Say you have these entries:

Kim Lee
David Bruce
Flora Dana


Any of these could be first,last or last,first - without more information
(from the person directly, say) it's impossible to tell.

This is one reason most developers routinely have separate firstname and
lastname fields.

Sorry, but you'll have to use a USB (Using Someone's Brain) interface, and
that may not be enough!

 

[John Spencer]

I agree with my colleagues about the difficulty of getting an accurate match.

What you asked about was a "FUZZY" match. I might try something like the
following. Hopefully, I got all the parentheses and other punctuation marks
correct. This might work as long as you have values in the namefield in table
1 that correspond with your statement of First space Last.

SELECT A.NameField, B.Namefield
FROM [Table2] As A INNER JOIN [Table1] as B
ON A.[Namefield] LIKE "*" & Left(B.[NameField],Instr(1,B.[NameField]," ")-1) & "*"
AND A.[Namefield] LIKE "*" & MID(B.]NameField],Instr(1,B.[NameField]," ")+1) & "*"



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County