Need help with finding like records in Access 2007 database

Posted on 2012-08-12
Last Modified: 2012-08-23
I have a table:

PFullName      Code      PLastName      PFirstName      NPI
John J Smith      Q-52021      Smith               John             123456789      
John J. Smith      Q-44522      Smith               John             123456789      
John J. Smith      Q-25947      Smith               John            
Jon      Smith      Q-78720      Smith               Jon              156435153
Jonathan Smith Q-58454      Smith               Jonathan      156435153    

How can I come up with a query(s) that would look at all the fields to see if there were possible duplicate entries for the same person but the name was not an exact duplicate
Question by:slatefamily
    LVL 15

    Expert Comment


    I think it's not clear what you are asking for. What does it menas "but the name was not an exact duplicate" ?

    You can group records by all fields except name ans list the groups with more than one record. Try something like this:

    SELECT Code, PLastName, NPI, count(*) as n
    FROM yourTableName
    GROUP BY Code, PLastName, NPI
    HAVING count(*)>1;

    I think it's not exactly what you are asking but I think it's a good aproximation.

    Hope it helps. Regards.
    LVL 26

    Expert Comment

    You could try using a soundex function.

    See this.

    Author Comment

    As you can see above the dictionary has a listing with a unique code Field name is Code  (Q-code) but when this was created there was nothing in place to try and prevent duplicates.  I want to run through the data in the dictionary to try and look at "potential duplicates".  

    The example above shows how a doctors name was entered/created into the dictionary but out of the 5 listed it is really only 2 people.  

    Some of this will be manual work to try and verify if the "potential duplicates" are the same person or a different person.

    Now the dictionary is set up with the NPI as a mandatory field and will act as a primary key, so that there can be no duplicate entries and they will get a message stating that there is already another provider listed with that NPI

    but I still need to clean this up so to do that I have to look for like names.  I have to go through this process because I will need to merge duplicate providers into one account due to attributions on each.
    LVL 26

    Expert Comment

    Did you look into the soundex function? It will help you narrow down possible duplicates, you can google for other fuzzy matching algorithms.

    Author Comment

    Does Soundex work on numbers?  Like if the name was completely different but they had 2 of the same NPI numbers?  I would need to do that type of query for each field.
    LVL 26

    Accepted Solution

    Soundex only works on names, but if you want to find duplicates in the NPI field you can use the "Find duplicates query wizard".
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    If I am understanding your request, ...The type of "Duplicate Detection" you are asking for here would be very involved.

    Perhaps it would be easier if you posted some sample data, then clearly indicated which entries would be designated as "duplicates"...

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now