Results 1 to 6 of 6
  1. #1
    BasicUser is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    3

    Deleting duplicate records based on most recent date

    Good afternoon,
    I am a brand new Access user so please bear with me. I have a large lot of information (approx. 400k rows) in a Table. There are many duplicates in this Table and I would like to remove those duplicates based on the most recent order (see example data below). I have been scouring the internets trying to find a way to do this but none that seem to make sense given my very limited experience. Can anyone provide me with some simple instruction on how to do this please? TIA!

    Sample Data

    Product Order Date Vendor
    1175 07/06/2018 AC America
    1175 08/09/2017 AC America


    1175 09/01/2019 AC America <---Only want to keep the last row of data (09/01/2019)

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why do you consider these to be duplicate records?
    This seems to be data from a multiyear database, are you not concerned with historical info?

    Deleting data is final. Are you certain this is what you want/need?
    Do you have backups? You should have.
    Are you working with a test database?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I agree with those comments. 400K records is not huge in Access db terms. If the db is approaching the 2 Gb limit, then I suspect there are other issues.
    The records you show are not duplicates, although they are similar. A record has to be taken as a whole to be considered a dupe.

    Assuming you have a valid reason for wanting to delete records that appear to be valid orders, consider trying this on a copy of your table.
    Code:
    Delete * from tblDeleteMax as T1
    Where Exists (SELECT [Product],Max([OrderDate]) FROM tblDeleteMax 
    Where Product = T1.Product 
    Group By Product Having T1.[OrderDate] < Max([OrderDate]));
    I have made up my own table name and tweaked date field name because I don't use spaces in any object name, and neither should you.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I also created a sample database and did the following (similar to micron) to delete the unwanted records.

    Created a query QmaxDatesByProd to get the Max orderdate for each Group

    Code:
    SELECT TestProds.Product
        ,Max(TestProds.OrderDate) AS MaxOfOrderDate
        ,TestProds.Vendor
    FROM TestProds
    GROUP BY TestProds.Product
        ,TestProds.Vendor;
    Then, created another query QryDeleteWhereNotExists to delete the unwanted records - the ones not in QryMaxDatesByProd

    Code:
    DELETE *
    FROM TestProds AS TP
    WHERE NOT EXISTS (
            SELECT "x"
            FROM QmaxDatesByProd AS Q
            WHERE TP.product = Q.product
                AND TP.vendor = q.vendor
                AND tp.orderdate <> q.MaxOfOrderDate
            )
    But, I recommend that you review my previous post and be cautious when "physically DELETING" records.

  5. #5
    BasicUser is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    3
    Thank you. I'm going to try these options and see I can get them to work. This is data I pulled from an internal report, so deleting isn't a concern. BUT, I have created a copy of the database in case I mess something up.

  6. #6
    BasicUser is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    3
    Forgive my ignorance, but where would I place this code to interact with my table?

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

Similar Threads

  1. Replies: 7
    Last Post: 04-11-2015, 10:19 AM
  2. Replies: 3
    Last Post: 03-11-2014, 07:32 PM
  3. Deleting Duplicate Records
    By EHittner in forum Queries
    Replies: 9
    Last Post: 10-30-2013, 02:56 PM
  4. Merging and deleting duplicate records
    By Rider in forum Access
    Replies: 1
    Last Post: 01-06-2012, 01:44 PM
  5. Replies: 3
    Last Post: 05-03-2011, 01:36 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