[Last Call] Learn how to a build a cloud-first strategyRegister Now


Need help with finding like records in Access 2007 database

Posted on 2012-08-12
Medium Priority
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

ID: 38285645

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 28

Expert Comment

ID: 38285652
You could try using a soundex function.

See this.

Author Comment

ID: 38285847
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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 28

Expert Comment

ID: 38285897
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

ID: 38286098
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 28

Accepted Solution

MacroShadow earned 2000 total points
ID: 38287813
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
ID: 38288477
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

829 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