Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Nov 20, 2021
Solved

Delete Duplicates (leave 1)

Hello, I have duplicates in my table and I want to delete only 1 of them. I have searched on the net for some code and found the below and fashioned it to my case.  I have an error though and it appe...
access
  • isladogs's avatar
    isladogs
    Nov 20, 2021

    Tony2021 

    Hi
    I'm just about to go out but have a look at the 2 example apps in the attached zip file which show two different approaches to this

     

    Hope one/both are of help

isladogs's avatar
isladogs
MVP
Nov 25, 2021

BrianWS1O 

To use the method you described, you should use a unique index for the fields that should not contain duplicates. Your suggestion of a composite primary key isn't necessary

BrianWS1O's avatar
BrianWS1O
Brass Contributor
Nov 25, 2021

True, either way works...if you're only using one field to identify duplicates. If you have to combine several fields to determine a unique record then as far as I know you'd need to create a multi-field PK

  • isladogs's avatar
    isladogs
    MVP
    Nov 25, 2021

    Not true.

    Setting a unique index works on a single field or multiple fields.

    There is no need to set those fields as a primary key for this purpose.

    In general, it is usually better to avoid the use of composite primary keys.

     

    • BrianWS1O's avatar
      BrianWS1O
      Brass Contributor
      Nov 26, 2021
      I don't follow; how do you set an index in MS Access across multiple fields? For example, if I want to avoid duplicates and I have records with First Name, Last Name, City, and State, but I don't want that same person at that location entered twice, how do I set up an index to prevent that?

      Also, you can create a composite PK just temporarily to append the records and weed out the dupes, then change back to using a different PK.

      Can you explain why a composite primary key should be avoided? Is it a processing thing? Does it matter with record sets below a certain size? Does it appreciably slow down processing when adding records? I'd like to see documentation to determine when this is or isn't a concern.
      • isladogs's avatar
        isladogs
        MVP
        Nov 26, 2021

        BrianWS1O 

        George has already shown you how to create a unique index for multiple fields.

         

        I have used composite primary keys in the past but eventually stopped doing so (except in rare cases) because they tended to make managing queries & code more unwieldy. If you run an online search you will find articles both for & against their use. A fairly balanced (if rather complex) article can be found at https://medium.com/@pablodalloglio/7-reasons-not-to-use-composite-keys-1d5efd5ec2e5 though I've only skimmed the article

         

        Lets take a simple reason for avoiding them. As well as not containing duplicates, primary keys cannot ever contain null values. So all those fields must ALWAYS contain data which at times may prevent a new record being saved. However, with a unique index, you can choose to ignore nulls.

         

        Hope that helps

Resources