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    
post Nov 14 2017, 10:34 PM

Posts: 526
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?

Go to the top of the page
post Nov 15 2017, 01:38 AM

Posts: 2,547
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.

WITH cteDupes (FirstName, DupeNum)
-- 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
FROM cteDupes
WHERE DupeNum > 1;
Go to the top of the page
post Nov 15 2017, 07:24 AM

UA Admin
Posts: 33,963
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
post Nov 15 2017, 01:38 PM

Posts: 526
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.
Go to the top of the page
post Nov 16 2017, 04:54 AM

Posts: 875
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    20th November 2018 - 10:35 AM