?
Solved

Need VBA code to delete duplicate records in a table

Posted on 2013-06-06
17
Medium Priority
?
907 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39226232
post the sql statement that you are using..
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

752 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