Results 1 to 3 of 3
  1. #1
    Bdowns is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    28

    Delete duplicate records while keeping one of the records

    The records I am considering duplicate are only equivalent in two fields. I want to delete all duplicates of the record after the first one. I know how to find the Unique ID of the records I want to delete using a group by query selecting the last of ID where count >1, but I don't know how to delete these records. Any ideas?

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Do you really need to actually physically delete them?
    If you use an Aggregate Query to filter them out, would that be sufficient?

    It can be done pretty easily using two queries.
    1. Create an aggregate query that groups by your two fields, and then takes the MIN of your Unique ID field.
    2. Now, you can link the Aggregate Query created above back to your original table on the Unique ID field to get all the records you want (with all the fields you want to return).
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    philipscodes is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Location
    Dubai
    Posts
    20
    Worked well for me.
    Thanks JoeM

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-19-2013, 11:28 PM
  2. Replies: 2
    Last Post: 04-13-2012, 12:53 AM
  3. Delete Duplicate Records Based on Criteria
    By chadd in forum Queries
    Replies: 2
    Last Post: 02-07-2012, 04:24 PM
  4. Replies: 1
    Last Post: 05-21-2010, 02:22 PM
  5. Delete duplicate records
    By Zukster in forum Queries
    Replies: 1
    Last Post: 08-26-2009, 03:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums