UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Utility To Delete Duplicate Records, Access 2013    
 
   
merlenicholson
post Nov 14 2017, 10:34 PM
Post#1



Posts: 504
Joined: 23-November 11
From: Tampa, Florida, USA - UTC -5:00


Looking for a utility to quickly and easily delete duplicate records in any table. I feel there should be a wizard-like form-driven add-in or something. Has anyone written this?

I have to do this routinely (a lot!) because of data I receive are mostly from non-IT people. I have two methods, both tedious. One, write the SQL manually from a template and substituting table name and controlling field names. Two, an Append query of the dups table and append into an empty non-dups version -structure only- with the primary key set to the controlling fields. Both work fine. The first method gives me many chances for editing errors, and the second duplicates the table and sometimes I run out of workspace and have to copy them to an empty db. Did I say sometimes they are LARGE?

Am I going to have to write this myself? Anyone interested in this?

Thanks!

--------------------
Merle Nicholson
Ninety percent of all mental errors are in your head. - Yogi Berra -
Go to the top of the page
 
MadPiet
post Nov 15 2017, 01:38 AM
Post#2



Posts: 2,253
Joined: 27-February 09



One of the reasons I'm not a huge Access fan.

Here's a SQL Server one... You could add more fields if you wanted... make the SQL dynamic, and then replace FirstName with a comma-separated list of fields. You'd have to use ExecuteSQL() to run it, though.

CODE
WITH cteDupes (FirstName, DupeNum)
AS
-- define the CTE query
(
    SELECT FirstName
                   -- the next line numbers the duplicates for each instance of a name
        , ROW_NUMBER() OVER (PARTITION BY FirstName ORDER BY FirstName) AS rn
    FROM Test
)
-- now delete any record that's not #1
DELETE
FROM cteDupes
WHERE DupeNum > 1;
Go to the top of the page
 
GroverParkGeorge
post Nov 15 2017, 07:24 AM
Post#3


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


It might be worth someone's time to create such a routine, but it's not something I've seen yet.

On the other hand, if you are getting the data in the same format every time, you might well be in a strong position to write that routine for your own data....

--------------------
Go to the top of the page
 
merlenicholson
post Nov 15 2017, 01:38 PM
Post#4



Posts: 504
Joined: 23-November 11
From: Tampa, Florida, USA - UTC -5:00


Mad - thanks.

George - The tables I get are never the same. I reformat every one into my own structure, and the first thing to do is to remove duplicate records, otherwise it plays havoc on my own structured tables.

I agree, it may be worthwhile; I was looking for a little encouragement because I'd do it only if others on the forum could find some use for it. It's not a small job, although I have all the pieces in other code projects. It seems strange to me that it's not in that short list of wizards.

--------------------
Merle Nicholson
Ninety percent of all mental errors are in your head. - Yogi Berra -
Go to the top of the page
 
nvogel
post Nov 16 2017, 04:54 AM
Post#5



Posts: 811
Joined: 26-January 14
From: London, UK


There are many data integration tools ("ETL tools") that will help you automate your data loading, cleaning, de-duplication, etc. Microsoft SQL Server has Integration Services bundled with it, although Integration Services can work with any DBMS, including Jet/Ace.

Most of the other tools of that kind are DBMS-independent, e.g.: Pentaho, Talend, Informatica, IBM DataStage.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 05:53 PM