[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Get rid of duplicate records on Import


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.
3 Solutions
Use ...SELECT DISTINCT [First...
Dale FyeCommented:
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.
DISTINCTROW would throw out complete records which were duplicates.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Preview? This may be not prctical for, say,100 000 records.
Use DISTINCT and primary keys appropriately.
Dale FyeCommented:
<This may be not prctical for, say,100 000 records>

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

--TripWire--Author Commented:
I meant duplicate records.  The entire row was copied.  Thanks for the answers.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now