Consolidate Multiple Names

[psquire]

Each month I get the previous months data. In that data, I have a list of names that have duplicates in them. Some of the names have last, first middle. Some have just last, first. I want to combine the like names and leave the unlike names not combined.

I want to combine in this situation


ex. Smith, Peter
Smith, Peter W

I do not want to combine in this situation
ex. Smith, Peter M
Smith, Peter W

My goal is to get the original name in a table with the combined name next to it.

Original Name	New Name
Smith, Peter	Smith, Peter
Smith, Peter W	Smith, Peter

[Dal Jeanis]

1) If I were you, I would go the other direction - in the example change "Smith, Peter" to "Smith, Peter W". That way, if "Peter M" joins the company after "Peter W" leaves, their historical data won't get consolidated.

2) Definition problem: If you have "Smith, Peter" and "Smith, Peter M" and "Smith, Peter W", then which ones get merged?

Suggestion: First, for analysis, create a tblNameAnal - you need a table to get a primary key - with the following fields:

PK, LastName, FirstName, MiddleInit, FullNameWithInit, FullNameWithBlank, FullTargetName

You load your monthly data into fields 2,3,and 4. Field 1 autonumbers for you. You make an update query to load fields 5, 6 and 7. Field 7 gets the same as field 5 to start, and you'll update it later.

Next, here's the query qryNameActDups to find all the actual dups, where there exists a record without middle initial and another one with middle initial

SELECT NA1.FullNameWithBlank, Count(*)
FROM tblNameAnal As NA1, tblNameAnal As NA2
WHERE (NA1.FullNameWithBlank = NA2.FullNameWithInit
AND NA1.PK NOT EQUAL NA2.PK)
GROUP BY NA1.FullNameWithBlank;

If any of the Count(*) are greater than 1, you have an ambiguity problem, where there are two different middle initials and a blank. this requires human intervention.

If all is well, then you can run an update query to create your FullTargetName field. Hmm. I'm not going to get this syntax right, so I'd probably break it down into steps.

UPDATE tblNameAnal AS NA1
INNER JOIN tblNameAnal AS NA2
ON  NA1.FullTargetName = NA2.FullNameWithBlank
SET NA1.FullTargetName  = NA2.FullTargetName
WHERE NA2.FullTargetName NOT IN (SELECT FullNameWithBlank FROM qryNameActDups)

I think that's right.

NA1 must have no middle initial, or its TargetName couldn't match NA2's FullNameWithBlank.
NA2 must have a middle initial, or because it is not in qryNameActDups
So we set NA1.Targetname to NA2.Targetname, and we're good.

Once you have all that, then you update your regular information tables

UPDATE MyTable As M
INNER JOIN  tblNameAnal As NA1 ON M.MyNameWithInit = NA1.FullNameWithInit
SET  M.MyNameWithInit =  NA1.FullTargetName;

[orange]

I agree with Dal in a corrective approach. However, I'd like to go back a bit and ask What are these names for? If all you have is a name with varying spellings and formats, you could be chasing this forever. Even if you clean up as best you can, the supplier may give you more "junk".
If you can associate a name with a unique identifier, and share that table with your supplier, then the problem could be greatly reduced. But if you have an "authoritative table" and the supplier doesn't, I think you're in a perpetual "Ill clean up the supplied data" scenario.

The degree of effort and approach seems to be dependent on how critical 'MESSY" data is to your business.
Good luck with your project.

[Dal Jeanis]

Good point.