Results 1 to 2 of 2
  1. #1
    chronjy is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2021
    Posts
    4

    sql statement -no duplicate and most recently data,,

    hello, i try to get a result of deleting duplicate data with most recently
    i have fields named, l,m,s,state, date1

    id l m s state date1
    1 me un 3 n 2021-04-02 1:05:33 PM
    1 me un 3 y 2021-04-02 1:09:33 PM



    i attached image below. which i hope to get this result



    i want to know the sql statement, is it possible?
    Click image for larger version. 

Name:	2021-04-03_220154.png 
Views:	13 
Size:	14.7 KB 
ID:	44897

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can do it via queries but it gets a bit complicated as you need to create a few queries to help you.

    The first one would identify the duplicates - you need to set it up as a totals query grouping by the field(s) that would define duplication ([l],m],[d] in your example), Count([ID]) with >1 in the criteria field and Max([Date1]) to get the latest date.

    Then you need to create another query in which you join the original table to the first query by the grouping fields and MaxOfDate1 to Date1. This will give you the ID you want to keep (notice in your example you say you want to delete the record with "most recently data" but in the result you show you keep it).

    Now you have all the pieces needed to do your delete but unfortunately you cannot use totals queries in actions queries like DELETE, INSERT or UPDATE. So you need to convert the second query into a make table to create a local table with the ID to delete and use this in the delete query.

    You could use domain aggregate functions (dMax, dCount, etc.) to do some of the above but they are usually slower then the queries. Or better you can do it VBA.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 4
    Last Post: 01-25-2017, 07:56 AM
  2. Replies: 14
    Last Post: 06-03-2015, 06:53 PM
  3. Replies: 3
    Last Post: 03-01-2013, 12:41 PM
  4. iif statement with duplicate name fields
    By focosi in forum Programming
    Replies: 8
    Last Post: 08-22-2012, 01:47 AM
  5. Replies: 4
    Last Post: 04-23-2012, 05:07 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