Solved

Need VBA code to delete duplicate records in a table

Posted on 2013-06-06
17
894 Views
Last Modified: 2013-06-20
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
0
Comment
Question by:SteveL13
  • 9
  • 8
17 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39226022
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
 

Author Comment

by:SteveL13
ID: 39226105
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39226232
post the sql statement that you are using..
0
 

Author Comment

by:SteveL13
ID: 39226653
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39226795
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
 

Author Comment

by:SteveL13
ID: 39226805
Am getting an error.  See attached file.
Error.jpg
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39226861
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
 

Author Comment

by:SteveL13
ID: 39226985
Now getting the new attached error.
Error2.jpg
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39227143
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
 

Author Comment

by:SteveL13
ID: 39227175
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39227246
<Still another error>

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

just tested it and it worked...
0
 

Author Comment

by:SteveL13
ID: 39227266
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39227365
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
 

Author Comment

by:SteveL13
ID: 39227405
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39227804
check your query, you have too many closing parentheses ")"
0
 

Author Comment

by:SteveL13
ID: 39229181
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
 

Author Comment

by:SteveL13
ID: 39231757
Just checking... any thoughts on how to speed this process up?
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 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

22 Experts available now in Live!

Get 1:1 Help Now