Get rid of duplicate records on Import

Posted on 2011-04-29
Last Modified: 2012-06-27

I have a form in a MDB with a command button on it with the following code...
CurrentDb.Execute "INSERT INTO tblInput ([FName],[LName]...etc) SELECT [FirstName], [LastName],...etc FROM tblTransfer WHERE [ThisField] IS NOT NULL, dbfailonerror

Open in new window

This code is meant to import the records from one Access (temporarily) table and append them to an existing table.  The problem is, some records are showing up twice.  Please help.
Question by:--TripWire--
    LVL 1

    Accepted Solution

    Use ...SELECT DISTINCT [First...
    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    What defines duplicate"  Is it First/Last name, or are there additional fields that would define that  (lots of John Smiths in the world).  Select Distinct would work, but if any of the latter fields are different, then you would still get multiple John Smiths.

    Are there lots of these?  If not, I prefer to preview the duplicates and delete them from the source table before I do the import.  As an example, lots of times, the couple of key fields (first/last) will have duplicates, but one record may have more or more current data in the other fields that another.  If I preview these record, I can specifically determine which to delete prior to doing the import.
    LVL 44

    Assisted Solution

    DISTINCTROW would throw out complete records which were duplicates.
    LVL 1

    Expert Comment

    Preview? This may be not prctical for, say,100 000 records.
    Use DISTINCT and primary keys appropriately.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    <This may be not prctical for, say,100 000 records>

    That is why I asked: "Are there lots of these?"


    Author Comment

    I meant duplicate records.  The entire row was copied.  Thanks for the answers.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now