Comparing Name Strings

[Joe Z]

I am trying to find matches between two tables using the name field. Table A has 115 records and I am trying to pull the account number from Table B, which has over 500,000 records. The problem I'm facing is that the two name fields are not in the same format. For example:

Table A Name - John Doe
Table B Name - Doe, John -or- Doe John -or- John M Doe -or-
Doe, John M (you get the idea)

How can I write a query to pull from Table B those records that are similar to the name in Table A. The above example is a very simple case. The names are not limited to Individual people, but can also be Company names. I am just trying to run one query which gives a list of potential matches without having to search each of the 115 names from Table A individually.

Any help would be greatly appreciated!!

Joe Z

 

[craw]

i believe you are going to have to create a primary key for your tables. even if you matched on part of the name

example) table A: John Doe matching to table B: Doe, John

there could be a case where there are two johns or two does... access needs something unique in the two to distinguish.

 

[Mile-O]

Don't you have any key to identify the names?

 

[Joe Z]

The two tables that I am trying to compare are housed on two separate systems. Therefore, there are no keys or standard linking mechanisms. This is a problem because I'm forced to compare the name fields, which do not have the same structure.

 

[craw]

The two tables that I am trying to compare are housed on two separate systems.

i assume you mean two different databases? regardless of where your information may reside... you must create a unique identifier... maybe you can create an intermediate form to show the user a list of possible names for the name that they are looking for. (that sounds a little wordy...what i mean is,)
Say your user wants info on John Doe. so they type in this name and your form returns all matches for John Doe - doe, john OR john doe OR john j doe... etc. then they can choose the one they are interested in, provided there is something that will distinguish one john from the next... like a work address or state of residence... i'm not sure how you can distinguish between johns... if your table with 500k records contains many variations of the johns how do you know that the one in your 115 record table is the right one. you may think about restructuring your smaller table.