Comparing Name fields from two separate tables using Access 2010

[cliff.clayman]

I have two Name fields in two separate tables that I need to compare to see if they match. The problem is that the formatting in both is not always the same. Here are the different formats:

Smith, John
John Smith
John A. Smith
Smith, III, John
Estate of John Smith
Estate of John Smith, Jr.
NULL



How can I go about comparing these fields? I've tried extracting the last name, but it's difficult with the "Estate of John Smith" example. Can anyone help me here?

[orange]

As I mentioned before(different post) look for patterns.
If the phrase/string "Estate of" is not something you want in your result set,
you can check to see if it is included in the input, ignore it and rewrite the string for further processing.

Similarly, you can check for common elements, such as:
Ms Miss Mr Mrs Rev Dr PhD BS BSc BA MA MSc Sr Jr II III IV ......add as necessary
and process as appropriate.

[CJ_London]

A lot is going to have to be by manual inspection - look for patterns, match those off then look for the next pattern

to look for same words you could use a vba function to make a comparison and return any sort of match

basically split each name on a space into an array then compare all the elements of one array with all the elements of the other array

very basic but something like

public function comparestrings(string1 as string, string2 as string) as integer
dim I as integer
dim j as integer
dim sarr1() as string
dim sarr(2) as string

comparestrings=0
sarr1=split(string1," ")
sarr2=split(string2," ")
for I=1 to ubound(sarr1)
    for j=1 to ubound(sarr2)
        if sarr(I)=sarr(j) then comparestrings=comparestrings+1
    next j
next I
end function

the higher the value comparestrings returns the closer the match

so put the above code in a module and call it in a query - then sort on the result - at least it will give you some focus on possible matches

Code will probably need to be modified to protect against null values

sql would be something like

select table1.id, table1.fullname, table2.id, table2.fullname, comparestrings(nz(table1.fullname,"ignore"), nz(table2.fullname,"ignore")) as Score
from table1, table2

and will not doubt take a long time to run - if each table has 1000 records, that is 1000*1000 comparisons and assuming each name has 3 component parts you can multiply that figure by 9 - so around 9 or 10 million comparisons. But you may have ways or reducing that