Need VBA code to delete duplicate records in a table

I need VBA code to find and delete duplicate records in a table.  If the field named "Sales Record Number" has duplicate values (it is a text field), delete all but the first record so only one is left.  The table name is "tblAllCleanedRecords".

--Steve
SteveL13Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
check your query, you have too many closing parentheses ")"
0
 
Rey Obrero (Capricorn1)Commented:
i'll assume you have a unique ID field for each record.

delete *
from tblAllCleanedRecords
where tblAllCleanedRecords.ID Not In(Select min(A.ID) from tblAllCleanedRecords As A
where tblAllCleanedRecords.[Sales Record Number]=A.[Sales Record Number])
0
 
SteveL13Author Commented:
Yes.  Unique ID field for each record.   I copy/pasted your code into a query and ran it.  For the test I have a total of 19,923 records in the table.  Each record is duplicated two times so the final record count after the query runs should be 6,641.  But it is taking forever.  Am I doing something wrong?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Rey Obrero (Capricorn1)Commented:
post the sql statement that you are using..
0
 
SteveL13Author Commented:
This is it...

DELETE tblAllCleanedRecords.ID, *
FROM tblAllCleanedRecords
WHERE (((tblAllCleanedRecords.ID) Not In (Select min(A.ID) from tblAllCleanedRecords As A
where tblAllCleanedRecords.[Sales Record Number]=A.[Sales Record Number])));
0
 
Rey Obrero (Capricorn1)Commented:
try this one


DELETE tblAllCleanedRecords.ID, *
FROM tblAllCleanedRecords
WHERE (((tblAllCleanedRecords.ID) Not In (Select min(A.ID), A.[Sales Record Number] from tblAllCleanedRecords As A
Group by A.[Sales Record Number])
0
 
SteveL13Author Commented:
Am getting an error.  See attached file.
Error.jpg
0
 
Rey Obrero (Capricorn1)Commented:
DELETE tblAllCleanedRecords.ID, *
FROM tblAllCleanedRecords
WHERE tblAllCleanedRecords.ID Not In (Select min(A.ID), A.[Sales Record Number] from tblAllCleanedRecords As A
Group by A.[Sales Record Number])
0
 
SteveL13Author Commented:
Now getting the new attached error.
Error2.jpg
0
 
Rey Obrero (Capricorn1)Commented:
try this


DELETE tblAllCleanedRecords.*
FROM tblAllCleanedRecords
where tblAllCleanedRecords.ID Not IN(select min(A.ID) from tblAllCleanedRecords as A group by A.[Sales Record Number])
0
 
SteveL13Author Commented:
Still another error.  See attached.  But I think I'm trying to go about this the wrong way anyway.  Here is what is going on.  I have code set up to import an Excel file into an Access table.  My fear is that the user may accidently import the same Excel file into the Access table again by mistake.  So I have been trying to figure out a way to delete the duplicate records if this were to happen.

Maybe I just need to find a way to NOT LET them import the file again.

???

--Steve
error3.jpg
0
 
Rey Obrero (Capricorn1)Commented:
<Still another error>

did you copy and paste the last SQL statement i posted..

just tested it and it worked...
0
 
SteveL13Author Commented:
Sorry.  Yes I just did and it ran just fine.  Unfortunately after it ran and I took a look at the resulting table I realized I made a mistake.  I need to delete ALL records that have duplicate vales in all fields (except the primary key field of course) and keep the first one that was written to the table.

Can I change what you've provided somehow and might it work?

I'm very sorry for my own confusion.
0
 
Rey Obrero (Capricorn1)Commented:
add all the rest of the fields in the " group by " clause


DELETE tblAllCleanedRecords.*
FROM tblAllCleanedRecords
where tblAllCleanedRecords.ID Not IN
(select min(A.ID) from tblAllCleanedRecords as A
group by A.[Sales Record Number]
               , A.[field name]
               , A.[field name2]
               , A.[field name3])
0
 
SteveL13Author Commented:
Here's my code but now another error.  See attached.

DELETE tblAllCleanedRecords.*
FROM tblAllCleanedRecords
where tblAllCleanedRecords.ID Not IN
(select min(A.ID) from tblAllCleanedRecords as A
group by A.[Sales Record Number]
               , A.[User ID]
               , A.[Buyer Fullname]
               , A.[Buyer Phone Number])
               , A.[Buyer Email])
               , A.[Buyer Address 1]
               , A.[Buyer Address 2]
               , A.[Buyer City])
               , A.[Buyer State])
               , A.[Buyer Zip]
               , A.[Buyer Country]
               , A.[Item ID])
               , A.[Item Title])
               , A.[Sale Price]
               , A.[Payment Method]
               , A.[Sale Date])
               , A.[Custom Label])
error4.jpg
0
 
SteveL13Author Commented:
If only I could type!  (or read I guess!)  

Anyway, that did it.  I named the query "qryDeleteDupeRecordsIntblImportRecords" just for reference.

Just one issue...  I had created 6,650 records in the table, 9 of which were duplicate intentionally.  The de-duping process took 2 minutes on my computer.  Is there any way to speed that up?

Here's my code to run the query from a command button:

    Dim db As Database
    Set db = CurrentDb
    db.Execute "qryDeleteDupeRecordsIntblImportRecords", dbFailOnError
0
 
SteveL13Author Commented:
Just checking... any thoughts on how to speed this process up?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.