Duplicates in an Access table

I have duplicates in a an Access table and I need to delete them. How do I do that in VBA?  Below is the code which identified the duplicates

DoCmd.RunSQL "SELECT [EUS02_SUBS_SSN], [EUS28_REND_PRV_NPI], [EUS02_PROC_CODE], [EUS02_SRV_DATE], [EUS02_EUS_ID], [EUS02_PRV_ID], [EUS03_PATIENT_CONTROL_NO], [EUS03_BILLING_TAX_ID], " _
    & "[EUS03_BILLING_TAX_ID_SUFFIX], [EUS03_RECEIVE_DATE], [EUS03_SUBMITTER_ID], [EUS28_REND_PRV_LST_NAME], [EUS28_REND_PRV_1ST_NAME], [Month/Year], [Directed Payment], [EUS28_CLM_PRV_SPEC_CD], " _
    & "[EUS03_PLACE_OF_SRV], [EUS02_GRP_ID], [MBR00_SSN_REF_NUM], [EUS03_SUB_PRODUCT_TYPE] INTO tblDupEncounters  " _
    & "FROM tblEncouterResultsFinal " _
    & "WHERE (((tblEncouterResultsFinal.[EUS02_SUBS_SSN]) In (SELECT [EUS02_SUBS_SSN] FROM [tblEncouterResultsFinal] As Tmp GROUP BY [EUS02_SUBS_SSN],[EUS28_REND_PRV_NPI],[EUS02_PROC_CODE], " _
    & "[EUS02_SRV_DATE] HAVING Count(*)>1  And [EUS28_REND_PRV_NPI] = [tblEncouterResultsFinal].[EUS28_REND_PRV_NPI] And [EUS02_PROC_CODE] = [tblEncouterResultsFinal].[EUS02_PROC_CODE] And " _
    & "[EUS02_SRV_DATE] = [tblEncouterResultsFinal].[EUS02_SRV_DATE]))) " _
    & "ORDER BY tblEncouterResultsFinal.[EUS02_SUBS_SSN], tblEncouterResultsFinal.[EUS28_REND_PRV_NPI], tblEncouterResultsFinal.[EUS02_PROC_CODE], tblEncouterResultsFinal.[EUS02_SRV_DATE]"

Thanks, Scott
Scott PalmerData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Is there an autonumber primary key in this table?
0
Dale FyeOwner, Developing Solutions LLCCommented:
This doesn't actually identify duplicates, it just sorts the records.

What fields in that query would define a "duplicate", or do they all have to match in order to be a duplicate?
0
PatHartmanCommented:
The simplest way to purge duplicates is to create a new, empty table with the proper unique index to prevent duplicates.  Then run an append query that copies the data from the original table and appends it to the new table.  At the end of the query, you will get an error message telling you that x records were not appended due to key violations.  Click OK.

After the purge, add an autonumber primary key but leave the unique index on the multi-field group so you can prevent future bad data from being added.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PalmerData AnalystAuthor Commented:
Sorry, left for the day right after I submitted this question and was gone for the weekend.

The records can have different information in some of the fields and still be considered a duplicate.  The fields below would define a duplicate:

EUS02_SUBS_SSN
EUS28_REND_PRV_NPI
EUS02_PROC_CODE
EUS02_SRV_DATE

Thanks,
Scott
0
aikimarkCommented:
Is there an autonumber primary key in this table?
0
PatHartmanCommented:
Did you try what I suggested?
These four fields need to be defined as either the primary key (not recommended) or as a unique index:
EUS02_SUBS_SSN
EUS28_REND_PRV_NPI
EUS02_PROC_CODE
EUS02_SRV_DATE
0
Scott PalmerData AnalystAuthor Commented:
No, unfortunately the way it works is each time a record is submitted to us a new identification number is created so there are some fields with different values, but it is still a duplicate.
0
PatHartmanCommented:
EXACTLY which fields define a duplicate?  THOSE are the fields that you define as the unique index.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
identification number is created
Is this an autonumber field?
0
Scott PalmerData AnalystAuthor Commented:
Okay, was not aware the indexing of the four fields would only get duplicates if all four fields were the same.  That will work.

Thanks for your help.
0
PatHartmanCommented:
You're welcome.  If sorting will get the preferred duplicate to be first, you can sort the data in the append query so that the preferred record gets added and the others are discarded.  If you don't care, then there is no need to sort the append query.  Keep in mind that having an autonumber as the PK is generally preferred for a number of reasons but you will still need the unique index to prevent business rules duplicates.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.