Matching names that are similar but do not match

[AnalystNY1985]

Hello,

I am in Access 2007 and am having a problem writing a SQL query to match names that are similar, but do not match. Here is the scenario:

Table1
FirstName
LastName
Employer
Staff ID (PK)

Table2
Payment
Payee
Date
PaymentID (PK)

I am trying to match the payment information in table 2 to the person who was paid in table1. However, the PAYEE (name on the check) can be written to the employer OR the full name of the staff member. Problem is the Payee field is not identical to the EMPLOYER or the Full Name fields, but are incredibly similar.

I need a query that can capture the similarities and pull matches. I have tried so many things! But because I am learning this as I go, this is incredibly hard. This query is time-sensitive, and anyone who can help-really, it would be greatly appreciated!

Thank you.

 

[jdraw]

Matching on names is an age old problem. There is no single or best solution. The key term in your post is "similar". A lot of people have "developed algorithms/routines" to do matching and many work well enough to meet their local needs. But there will always be some anomalies that don't quite work for one reason or another.

My recommendation would be to use some vba routines - either do some searching and find something, or develop/adjust something for your own circumstances.
You will probably be dealing with Instr, Mid, Right, Left type functions.

Another point is how many such names are involved?
Is this a one time thing or ongoing?
From a database design (and company policy view) to uniquely identify an employee is basic and critical. No one would depend on names for uniqueness.

To make this meaningful in the extreme, consider some of the issues for bank accounts for John Smith. Which John Smith - no initials, no DOB, no address, no phone number, no driver license, no unique ID etc, etc.???

Enough soap box - many of us here are willing to help. Gives us more info and describe the environment and urgency and I'm sure help is available.

 

[AnalystNY1985]

Thank you very much for reaching out. I have run the following SQL queries and have come up with results- but I am pretty sure it is not capturing everything.

SELECT *
FROM Table2 INNER JOIN Table1 ON Table1.GroupNAME Like '*'+Table2.CheckNAME+'*';

SELECT *
FROM Table2 INNER JOIN
Table1 ON (Table1.FirstNAME+' '+Table1.LastNAME) Like Table2.CheckNAME;

These queries have been pulling matches, but I am not sure that the information is exhaustive. There are about 800 names to match about 10000 checks, and I have to do this in 15 instances.

Problems: there is a middle initial that may be in the check name and I do have the list of middle initials (but data is incomplete if they do not have one registered or do not have a middle initial!) The name may be grouped together if the data entry was typed in wrong- so my first name + last name may not have a space in between on the Check name, but the first and last names in table 1 are in different fields, and I don't know how to capture that condition.

Also, the name may be abbrevieted or cut off, so I am trying to create the wildcards to capture anything like the first name only, last name only, group name, or any combination thereof.

I have 6 queries so far, to match first name only at least, last name only at least, first and last name at least, first and last name equal, group name at least, and group name equal (variations of the above two SQL queries) but I do not know if I am capturing all of my inconsistent Check names.

Because the check could be written to the group or the name of the staff member, I have to account for all of these variations, and I am lost.

On top of all of this, this is not my database, I only have access to the excel reports that have been generated, and have no information otherwise. Working only with what I can, and have no prior experience in SQL or Access.

Again, thank you for reaching out, and I look forward to hearing from you.