Results 1 to 5 of 5
  1. #1
    gutenberg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    31

    Problems creating a "delete query" to delete duplicate rows/records

    When I create a "delete query" it deletes everything in the table (table is named (Deliveryorderalternative). I need it to delete only the duplicate rows/records (under the AlternativeDesc category) while leaving the 1st original row/record intact. What is the procedure to accomplish this?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why do you have duplicates? Seems there is a structure or logic problem.
    Delete queries can be very unforgiving --- test them thoroughly before executing.

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    You must first isolate the key field of the records you want to delete. This will probably require a series of queries. An Aggregate query is needed that results in all duplicates (i.e. Count >1). Then another Aggregate query that relies on the MAX function to identify the higher of the two autonumber fields (because you want to keep the original)....or possibly you have a time stamp field or some other field that could be used for MAX.

    A delete query cannot be linking to any aggregate query. So you will have to write your delete set to a temp table. Then join the temp table to the regular table for a delete query.

    I know what you describe sounds simple to a human but it is a somewhat complicated task to accomplish.

  4. #4
    gutenberg is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2016
    Posts
    31
    The table I’m using is called “PurchaseQuoteDtl”
    The text field that I’m using to look for duplicates in iscalled “GP Code Custom”.
    The field name for the primary key and the autonumber inthe PurchaseQuoteDtl table is called DSqId.
    How would I go about constructing code for a delete queryusing this data?

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    See this on deleting duplicate records http://allenbrowne.com/subquery-01.html

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

Similar Threads

  1. Replies: 3
    Last Post: 12-06-2014, 03:59 AM
  2. Replies: 13
    Last Post: 05-06-2014, 12:42 PM
  3. Replies: 1
    Last Post: 07-26-2013, 11:42 AM
  4. Replies: 4
    Last Post: 08-14-2012, 11:56 AM
  5. Replies: 11
    Last Post: 03-30-2011, 01:08 PM

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