how to match names spelled slightly differently

[influent]

I have several tables with people's first names and last names in them, one row for each person, no duplicates. I need to match the names in one table to the names in the other tables, but sometimes the names are misspelled, etc.
Here is an example:
Table1,Row1,FirstName = "AnnMarie"
Table1,Row1,LastName = "Smith"
and
Table2,Row5,FirstName = "Anne Marie"
Table2,Row5,LastName = "Smith"

Is there a way for me to match up the two rows, using the LIKE keyword, or something better?

 

[cmmrfrds]

There are 2 functions that may work for you. Check them out.

Select fname, lname
from customer
where difference (fname, 'anne marie') >= 3

Select fname, lname
from customer
where soundex (fname) = soundex('anne marie')

 

[MichaelRed]

but neither is native to Ms. Access.

soundex is generally available (and at least one version has been posted here [tek-tips]) but I'm not aware of the 'difference' procedure.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[influent]

Thanks you guys. I hate Access, so I don't care about that side of it.

http://databases.about.com/library/weekly/aa042901a.htm

 

[SQLSister]

Suggest that in the future when you design database structures that you avoid putting first and last names in more than one table and you won't have the problem of things which do not match up. Create an ID field instead to link the tables on. Names are a bad thing to use to identify a record in any event. What if you really do have a custome named Susie Jones and a different customer named Suzie Jones? With what you are trying to do, you might match them together as one person's records even though they actually are two people. You'll also have records that are impossible to match up because they are so far off. For instance what if in one table she is Anne Marie and in another table she is Anne?