How to find duplicates in name field?

[Rocketman1]

My data base has the entire name in one field, the "Name" field (data is imported from a data source), and there are multiple record for individuals. Sometimes the individuals name will include their Middile Initial, therefore FM does not see them a duplicates when executing a standard search for duplicates. For example:

Smith, John and Smith, John W. are not considered duplicates though they are the same person.

How can I get FM to see them as duplicates?

[Lee Smith]

I would parse out the first and last names into their own fields, and then do my duplicate flag based on a Concatenation of those two fields.

You could probably get by with

[color:blue]LastName (calculation, Text Result) =

LeftWords([color:blue]YourField, 1)

[color:blue]FirstName (calculation, Text Result) =

MiddleWords([color:blue]YourField, 2, 1)

and then do your duplicate check on a Concatenate the results.

[color:blue]CombinedNames (calculation, Text Result) =

[color:blue]LastName [color:green]& [color:blue]FirstName

HTH

Lee

[John Mark Osborne]

The word functions won't handle two word last names or first names so I think the Position function might work better in this scenario:

LastName = Left(Name, Position(Name, ",", 1, 1) - 1)

FirstName = Right(Name, Length(Name) - Position(Name, ",", 1, 1))

This still doesn't solve your problem with two word last or first names. So, forget the formulas above since all you really want to do is remove the middle name if it exists. Or, you could transfer everything but the middle name to another field. Start by creating a new calculation field called Name2 with the following formula:

Case(

Right(Name, 1) = ".",

Left(Name, Length(Name) - 3),

Name)

Now you will have a new field (Name2) with just the first and last name even if you have a last name like "Del Rio". You can search this new field for duplicates as long as the data entry is consistent.

[LaRetta]

When you finally get them split, Rocket, leave them split. It's VERY easy to put fields together but quite difficult to split them apart (for the very reasons Lee and John mentioned); particularly with names or addresses. There simply are too many variables. Only a PERSON can make proper distinctions and those distinctions should be made at the moment of data entry because only THAT person knows to ask the Customer if it's unclear. Getting a name right is very important!

My pet peeve is a computer determining how my NAME should be capp'd and lowercasing the R.

LaRetta

[Rocketman1]

Thank you, to all who responded

For my immediate need, Lee's suggestion did the trick. After I complete my immediate deliverable, I definitely will try the other suggestions, as well.

Thanks, again!! - Rocket