Complex Duplicate Search

[ddreese]

There are two files of importance here: "Significant Others" and "Household Members". Both of the files store the same sort of information, but the "Household Members" file needs to be as close to un-duplicated as humanly possible. In other words, in the "Significant Others" table, the same person can have multiple entries (records), but they cannot in the "Household Members" table.

The problem I have is that when a certain action occurs, all the records in the "Significant Others" that are related to a particular case need to be copied into the "Household Members" table and related to a NEW case in a different file. Now, persons coming in from the "Significant Others" may already have a record in the "Household Members" table, so they do not need to go in there again, they just need to be linked to the new case (easy part). The biggest problem with this is that you all know users don't always spell names the same way, especially in urban areas with VERY diverse names.

I need to create a script that will:

1. Loop through the "Significant Others" one at a time, and display any possible duplicates in the "Household Members" file. I'd like this to be as "fuzzy" as possible, which is probably a lot to ask, but it's the best way. In other words, I'd like CLOSE matches show up as well as direct matches. Dunno if this is possible or not.

2. The previous step will show a list of possible duplicates, if there are no results, it will go ahead and create the new person. If there are any possible duplicates, it will allow the user to view that persons demographic information and decide whether or not that is the person in question. If it is, then just link that already established record with the NEW case (easy part). Again, if none of the possible matches are the correct person, create a new one with the significant other's information.

3. Continue looping until finished with the 'Significant Others'

Basically, the looping and picking and choosing who is who is the easy part, I just need a way to display the results of multiple possible searches. For instance searching based on first name and SSN, or first name and last name, or last name and SSN, I'd like results from ALL of those to show up for the user to review.

Also, please don't question the design issues here, it is the way it is for a reason. Anyone working for any type of social service agency, state or local, will know that even the most seemingly simple concepts are far from that in the computer world. We've discussed many different designs, and no matter how we do it, we're going to have to do some sort of similar thing. And ultimately, yes, it's all coming down to the user to decide, but nothing will ever be perfect anyway.

Thanks for reading, I'd appreciate any help!!!

[Fenton]

I wouldn't question the design. I mean, people could have a multitude of significant others, a many-to-many relationship :-)

But each is only part of 1 household (I guess).

First create a calculation field, "NameAddress4," FirstName & " " & LastName & Left (Address, 4) in both files (if you have an address in both files; the Left(field, 4) decreases typos in the address, which are often at the end).

Create a relationship based on NameAddress4, from Significant to Household.

Loop or Replace a [x] mark into all records with a match. These are done, exclude from further searches.

Using the first few letters of names (and/or addresses), then visually scanning the resulting matches in a small portal, catches quite a few typos.

Put together some of the 1st name with some of the last name, in both files:

_cName4 = Left (First, 4) & " " & Left (Last, 4)

Create a relationship on that, from Significant to Household, for a portal; put the full names in the portal, along with whatever else you need, "Household_Name4."

Create another calculation, in Significants,_cCount_Name4 =

Count (Household_Name4::ID (or whatever))

Find for _cCount_Name4 > 1 and [x] mark (above) empty (=).

Sort by name.

Flip through records, looking at the portal.

There are going to be some false positives. But if one has a true hit [x] mark that record as done and Omit.

Whichever records are left are new. Import into Household.

Something like that. It's all kind of fuzzy :-/

[Ugo DI LUCA]

It won't straight solve your problem, but I posted a file called "Check Mispellings" in the Sample Section. You were talking about fuzzy

[ddreese]

Thanks for the responce, guys. Sorry I didn't get a chance to reply yesterday, I was out enjoying the lovely weather on the links! Something tells me I might have had a better day at work, though! hahaha...

I'll try your reccomendations and see if I can generate any further questions. Thank you!