Solved

Need VBA code to delete duplicate records in a table

Posted on 2013-06-06
17
897 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

856 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