3 sets of Data

[Eddyoftheyear]

Hi All,

I have a big project that I am working on and I am stuck. I don't have any clue of what to do. Any suggestions would be greatly appreciated.
We have 3 big departments merged together. Each department has list of providers used for services. We all share the same providers as needed.
The issue is that I have 3 huge tables with provider names. one of the tables has 1600, second 1800, and third 784.
I need to merge them to one file to consolidate the resources.
The issue is that one provider maybe in the 3 different tables under different spelling. Example: John M Smith and John Mathew Smith and third maybe John Smith. I am sure all are the same according to the department.
Any idea and suggestion on how to consolidate them.


Fields I have

First Name Last Name Date Department

 

[MajP]

There are lots of strategies for finding (candidate) duplicates. So you will build some different queries that identify the possible duplicates. But basically you will use different aggregate queries and check if the count is greater than 1.
Group by First, Last, Middle Department (pretty much a guarantee dupe)
delete dupes then
Group by Last, Department (possible match especially if a bizarre last name)
delete dupes
Group by First, Last (possible dupe if person changed department)

It will require human validation. But once you find a possible candidate, what do you want to do.? I assume choose one and then relate the child records of the other records to the choosen record. This is normally more involved than actually identifying the duplicates. The trick to doing this is making a user interface that lets you do the above quickly.