Results 1 to 4 of 4
  1. #1
    CzarBomba is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2016
    Posts
    4

    Struggling with find duplicates / delete duplicates operation

    First off, I was having someone assist with some data entry and long story short they copied the whole database from the network location and pasted it to their computer instead of using the short cut. Shame on them. I don't know exactly how long this went on but there ended up being quite a bit of information that we needed to get to the real database. So I basically ended up appending it to the appropriate table in the actual DB. Very, very dumb thing to do. Shame on me. We now have duplicate entries. About 90,000 of them. So I thought a good idea would be to run a find duplicates query. And then maybe a delete query to get rid of them (the duplicates returned by that query). Doesn't work that way apparently. I googled this issue and I searched the forums and I followed the directions but it's not working, or my understanding of how this needs to proceed is flawed maybe.



    Whatever the case may be, I can get the find duplicates query to work- I run the query and it returns the records that are duplicates. Great. However, the delete operation fails horribly (or succeeds depending on how you look at it). I made a copy of the table to do a dry run, and well the query doesn't just delete duplicates. It purges out the entire table.

    So can someone explain maybe how the delete query works? I believe that's where my issue is

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Delete queries can be very unforgiving so ---you have been fore warned.

    Make a backup of the table involved.
    Set up your criteria to identify what you are looking for, then use a READ ONLY SELECT query to verify the records you will be working with.
    Determine exactly how you will identify which record to delete.


    Review and test a DELETE query with some sample test data. See the DELETE query syntax and example.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Review: https://support.office.com/en-us/art...1-42A3E49C63AE

    Post your attempt SQL for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    each record must have a field that provides a unique ID. If that doesn't yet exist - then add an AutoNumber field

    then make a query with the field(s) that are duplicate - and also this unique field....i.e.
    1 yellow
    2 yellow
    3 blue
    4 blue
    etc

    option the query to be an Aggregate query (sigma symbol big E in ribbon); and for the duplicate fields you use the default Group, but for the unique field change that to Max.....and your result is this:
    2 yellow
    4 blue

    save that query with a name

    now make a new query; using this saved query as it's record source - - and option the query to be a MakeTable query - - run it - - and make a table with these values.....let's call this DUPES table

    now you make your delete query..... have the core table plus the DUPES table joined by the unique value... drag the * symbol of the core table onto the fields so it is the only field in the Delete query; also with your cursor in the upper half of the query design, right click, go to properties - and change the property Unique Records to Yes.

    then you should run the delete query and it should work

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

Similar Threads

  1. I've found duplicates but how do I delete?
    By rocktap in forum Queries
    Replies: 6
    Last Post: 04-20-2015, 07:08 PM
  2. Delete duplicates in a Querry
    By sdc1234 in forum Queries
    Replies: 1
    Last Post: 02-19-2014, 06:35 PM
  3. Delete all duplicates except one
    By Tom123 in forum Queries
    Replies: 5
    Last Post: 02-11-2014, 07:36 AM
  4. Replies: 8
    Last Post: 02-09-2014, 07:25 PM
  5. auto delete duplicates
    By sammiantha in forum Access
    Replies: 2
    Last Post: 04-17-2012, 02:37 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