?
Solved

Need VBA code to delete duplicate records in a table

Posted on 2013-06-06
17
Medium Priority
?
922 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

807 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