Find matching records with partial match

[CindySue]

I have two tables. One called Recruits that contains data from a career fair, with first and last names, date of birth, high school and some other info. I have a second table called Enrolled of students that have enrolled, with similar data, but not all fields are identical. I don't have a specific field that I can link on that uniquely identifies records, such as SSN, etc. I want to be able to compare and see how many of the prospective students that attended a career fair actually enrolled. Names aren't a good match, as they may put Liz instead of Elizabeth, etc., although last names should hopefully be the same. I thought to find matching birthdates, and then look at names and high school to determine if there is a match, and then click the Enrolled check box in the Recruits table if they do appear to be a match. The Enrolled table will eventually be deleted. When I create a find matched, it only finds those that match exactly, which doesn't work. What is the easiest way for someone that is fairly basic with Access and few to no programming skills to do this? Thanks for any assistance.

[Rabbit]

This is an almost impossible task if you're not going to do any programming.

You can always substitute money for programming by buying software or services that will do this type of matching for you.

Otherwise, you will need to program a method suited to your particular data. We have some articles on here related to fuzzy string matching. But you will need to combine that with other factors as well if you want to find the best match possible.

[ADezii]

@Rabbit:
You seem to be the real Expert in this area. In this case, what do you think about the idea of using SoundEx Value Comparisons on the Last and possibly First Names after an initial DOB comparison? I am thinking along the lines of Haldeman, Haldiman, and Haldimon would return the same SoundEx Value but on the other hand, Liz and Elizabeth would not.

[Rabbit]

In my experience, SoundEx is way too inaccurate in its matching. Double Metaphone is better for name matching because it takes into consideration the various ethnic origins of various names.

And there's not really a way to get around the whole nickname issue unless you download a database of them.

As for the birthdate, that's probably a good one to start with. But it sounds like all this data is more prone to data entry error than usual. I.E. it almost sounds like the data is comprised of a person writing down what another person is saying and then that is hand entered into a database as a later point in time.

I can see a digit easily getting transposed in the date of birth. Which means it might be good to run the date of birth through Levenshtein to find matches where a number might have gotten transposed or mistyped.

Ideally, you compare all like fields through a fuzzy string match and calculate some composite score.

[CindySue]

I'm not trying to automate the whole thing, just make it easier to compare. The only thing I care about matching exactly is the date of birth. I can look at the data and make a manual determination if they are a match or not, and then check a yes/no field in my Recruits database when I do find a match. I just need a reasonably easy way to compare birthdates (and corresponding names) in one database with the same info in the other. For example, if Sue Smith, Bill White, Bob Green and Mary Jones all have a birthdate of 1/1/2000 in the Recruits database, and Sue and Bill also are in the Enrolled database that I get from Admissions, I'd like to see the four from the Recruits database listed on one side and Sue and Bill listed on the right--or above and below each other. I could then check the Enrolled check box for Bill and Sue and then go on to look at the next birthdate to see if I have any matches. Just grouped on birthdates, maybe.

[ADezii]

1. Does the Date of Birth Field contain Values (not NULL) in both Tables?
2. A Sample of Data from both Tables would be a great help, stripped of any sensitive Data of course.
3. If you cannot provide actual Data, please provide the Structures of each, such as: Field Names, Data Types, Required?, etc.

[CindySue]

I finally just created a form with a subform with a relationship on date of birth field. I now see the records from Enrolled (which has the fewest entries) one at a time in the top, and all records in my Recruitment table that have a matching birthdate in the subform. I manually compare, and simply click the Yes checkbox when they match. I've always known with my limited data that I would have to manually compare them, I was just trying to find the easiest way to do that. Thanks to everyone that replied.