Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-11-2013, 09:57 AM   #1
WLCummings
Newly Registered User
 
Join Date: Sep 2012
Posts: 26
Thanks: 6
Thanked 0 Times in 0 Posts
WLCummings is on a distinguished road
Deleting Duplicate Records

I apologize if this has been asked and ans before. But i am a little frustrated now. I have a data base with a primary key but some records have been added from a excel data base and now the data base has duplicate records with different primary keys. I do a Query looking for duplicate records based on a field not the the primary key. There are 315 out of 22000. I can sort the table and then manually delete the dups. The Query does not allow deleting records so finding the culprits in the full Data base is time consuming. How can i delete the duplicate records using a query? Is this function available hidden someplace in the guts of Access? Seems like this would be a frequent problem. The Microsoft forum addresses this problem and the only viable solution is the manual method, when they get to the Query method it looks like they just forgot what the subject was and tell you how to delete one record if you know what the criteria is. If two non Key fields are Identical then i want to delete one of the records.

WLCummings is offline   Reply With Quote
Old 09-11-2013, 10:18 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
There's not a lot of info about how to handle that multiple duplicate situation because it's extreme. You want to check for duplicates before you create or import them -- because deleting duplicates is so complicated. And users might have now attached other data to the many dupes, so automating deletion becomes very risky that you'll lose information. Maybe one dupe has an address you need and another dupe has a couple of contacts you need. Tricky to automate.

I think of data in some ways like it's a tank of pure water. What you want to do is filter out the problems before you allow them into your tank. If you already have impurities--dupes--in your tank, start a new tank. Import stuff in line-by-line and don't allow dupes at that time. If dupes are encountered, import all their child data, and then delete them.

hth
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
WLCummings (09-11-2013)
Old 09-11-2013, 10:33 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,934
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Deleting Duplicate Records

You should make an aggregate query from your table. Bring down and GROUP BY every field that should be unique, then bring down the ID field and make it show the MIN. That query will show all the 'unique' IDs you want. Save it and call it 'UniqueIDs'.

Next,create a new query bringing in your table and the UniqueIDs query , link them by ID. Now you have a few options:

1. Make a new table with just the 'unique' records: In that new query, bring down all the fields from your table. Turn that into a Make-Table query and you have a new table with just the records you want.

2. Delete the 'non-unique' records: In that new query, make it a LEFT JOIN from your table to UniqueIDs. Bring down the ID field from both sources and put criteria underneath the UniqueID.ID of 'Null'. Turn that into a Make-Table query and you have a table with the records you want to delete. In a new query, bring in your initial table and the one you just created, link them by ID and delete the ones in the main table. Your main table will now have just the 'unique' records you want.

plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
WLCummings (09-11-2013)
Old 09-11-2013, 10:45 AM   #4
WLCummings
Newly Registered User
 
Join Date: Sep 2012
Posts: 26
Thanks: 6
Thanked 0 Times in 0 Posts
WLCummings is on a distinguished road
Re: Deleting Duplicate Records

Thanks for the quick reply's. I get the picture that you make sure you do not have a lot of dups. As it turns out I imported one month worth of Excel data twice. I now am deleting all data for August and reimporting it one time Of the other 22000 records only had 6 dups so deleted those manually.

WLCummings is offline   Reply With Quote
Reply

Tags
delete , duplicate , record

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting duplicate records BadgerLikeSpeed Modules & VBA 4 02-20-2013 08:06 AM
Deleting duplicate Records Kai.Evans Reports 5 05-16-2011 07:27 AM
Deleting Duplicate records using VBA Rockape Modules & VBA 1 04-18-2011 02:30 AM
Deleting Duplicate Records StevoC Queries 1 04-30-2008 01:34 AM
Deleting duplicate records jgnasser General 0 04-02-2004 02:10 AM




All times are GMT -8. The time now is 06:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World