Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello again. (Access 2007)
    Having a staff table with StaffID, FirstName, Surnames duplicated, how can I delete the duplicates within this table - without creating a separate
    Append Query.. (I know I could copy the table, create a primary key and append data, but don't wish to do it this way.)

    I believe this is a different query to my previous one. Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    How do you decide which record to delete if there are two with the same StaffID, FirstName, Surname?


  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    It's not entirely clear to me why this is a different query, and using an append query seems an odd way to get rid of duplicates. Are you saying you have two or more records with the same StaffID, the same FirstName and the same Surnames?
    Wendell

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hiya.
    I've a single table with duplicate StaffIDs, FirstName and Surnames. I want to delete duplicates - keeping any one of the duplicates.
    Thanks, Andy.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The Find Duplicates option in the Query Builder Wizard should be able to identify those duplicates, and then you will need to manually pick one record to delete. However I should note that if you have duplicate Staff IDs, then you have a fairly serious design issue with your table. If you have records of another type linked to the Staff ID, such as addresses or phone numbers, then you pretty much have a shambles.
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I agree with Wendell that you have a serious issue with your table design.

    But you could do the following:
    - Add an AutoNumber field ID to the table.
    - Create and run the following query:

    DELETE *
    FROM tblStaff
    WHERE DCount("*","tblStaff","StaffID=" & [StaffID] & " AND FirstName='" & [FirstName] & "' AND Surname='" & [Surname] & "' AND ID>" & [ID])>0



  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello.
    I was only giving an outline of my question - I agree that the table structures would be a mess.

    I can see that creating an AutoNumber field would help solve the problem. Thanks Andy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •