Results 1 to 7 of 7
  1. #1
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283

    Deleting Duplicates

    Hello,

    I am having trouble finding a video or an example query in how to delete all duplicates and leave just 1 record each.

    Table name is Table1, the field that I'm looking at that can have duplicates is "LON"

    For example
    LON


    1111
    1111
    1222
    1234
    1255

    I just want to keep one of the 1111's

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Use the query wizard to create a duplicates query, then change it to a delete query.

  3. #3
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    that will delete both of the records. I want to keep one of them

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Hah, you're right!

    See here: http://allenbrowne.com/subquery-01.html

  5. #5
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I don't have a PK in my table so I would not be able to do that. Unless inserting one is the only way?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It is using the lowest (first entered) ID to determine which duplicate record to delete. You can use any field in your table to make that determination, minimum or first or something like that.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    use a select distinct query to eliminate the duplicates - then convert to a make table query to create a new table

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

Similar Threads

  1. Deleting Duplicates
    By Guy Winfield in forum Access
    Replies: 8
    Last Post: 05-15-2015, 06:34 AM
  2. Replies: 3
    Last Post: 06-19-2014, 10:28 AM
  3. Passing Form Values - Deleting Duplicates
    By sonoamore in forum Programming
    Replies: 4
    Last Post: 12-07-2013, 02:09 AM
  4. Replies: 4
    Last Post: 07-15-2013, 12:30 PM
  5. Deleting Duplicates
    By TundraMonkey in forum Queries
    Replies: 4
    Last Post: 09-08-2009, 07:13 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