Delete duplicates with a LIKE statement

[Guest]

Hello,
I know this sounds troubling, but bear with me... The data is not
particularly important at the moment. Anyway, I have a field with first and
last names (yea, both in one field). Various spellings, some include
Mr./Mrs, some include PhD, etc. I am trying to do a LIKE statment to delete
the ones that appear to be really close to being duplicates, for example:

Adams, Jon E.
Adams, Jon E., DDS
Adam, Jon E.

Maybe not delete, but append just "pretty much" unique into a new table for
further review. I can figure that much out after I figure out how to
actually query the Name field (which is where I need help)... The Find
Duplicates query wizzard doesn't seem to have anything I can modify to make
it a "Like" query.

Any ideas? Thanks! Mike

 

[John Vinson]

Hello,
I know this sounds troubling, but bear with me... The data is not
particularly important at the moment. Anyway, I have a field with first and
last names (yea, both in one field). Various spellings, some include
Mr./Mrs, some include PhD, etc. I am trying to do a LIKE statment to delete
the ones that appear to be really close to being duplicates, for example:

Adams, Jon E.
Adams, Jon E., DDS
Adam, Jon E.

Maybe not delete, but append just "pretty much" unique into a new table for
further review. I can figure that much out after I figure out how to
actually query the Name field (which is where I need help)... The Find
Duplicates query wizzard doesn't seem to have anything I can modify to make
it a "Like" query.

Any ideas? Thanks! Mike

Is "Jon" "pretty much" like "Jonathan"? Not to a computer.
Is "Jon" "pretty much" like "Jane"? Maybe, to a computer.
Are "Fred E. Brown" and "Fred E. Brown" at the same address in Parma
Idaho the same person? Nope, they're father and son.
Are the names "Adams, Jon E." and "Adam, John" the same name? I don't
know how either a computer OR a human could decide without further
evidence.

You're attributing human intelligence to a lowly software program, and
I don't think it's going to succeed.

John W. Vinson[MVP]

 

[RobFMS]

Michael

I think I can safely say that the Access community is cringing for you at
the moment....

Here is some helpful information I would like to pass along.

1. Backup your data.

2. In the table where this data resides, create an additional column called
"WIP" (for Work In Progress).

3. Copy the contents of the existing names into this new column "WIP".

4. At this point, you will have two columns with the same data.

At this point forward, we are only going to be working with the new column
"WIP".

5. Remove all spaces and punctuation marks from the data.

If you have the following:
Adams, Jon E (1)
Adams, Jon E. (2)
Adams, Jon E., DDS (3)

If you notice that (1) and (2) are the same name with exception of the
period on the initial on (2)

You will now get:
AdamsJonE (1)
AdamsJonE (2)
AdamsJonEDDS (3)

If you notice that (1) and (2) are now identical.

6. Now, If you only take the distinct items, then you are left with the
following

AdamsJonE
AdamsJonEDDS


Now, this is not an exhaustive approach but it might help you narrow down
the names.

HTH

Rob





--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/

 

[Guest]

John,
I know what you are saying, it's just more of an experiment than anything
at this point and the data is not really that important in the grand scheme
of things. I just had a request to do this for someone and wanted to see how
close I could get.

I was hoping I could do a "like" statement in a query and then group the
results based on the like or something like that.