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?
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 FyeCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.