Results 1 to 8 of 8
  1. #1
    Treecat is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Sep 2019
    Posts
    4

    Query Newbie

    I am doing a data cleansing project to eliminate duplicate serial numbers for an item of materiel in a registry. For one item there are over 600,000 records and 23,000 duplicates. For this reason I am making my first foray into Access.
    Many fields will be in the report, but the key fields are serial number (SN), unique record identifier (UI), and registration date (REG-DT). For this request for help I will call the table TEST.
    I used the simple Find Duplicates Query wizard to identify the dupes, however, for audit reasons I cannot just delete them. I need to produce a report (or new table) that indicates for each pair, which record to keep and which to delete (a new field in the table called ACTION). Usually the record to keep is the oldest of the pair.
    Sample report:
    SN UI REG-DT ACTION
    54613648 D27777245500-90154613648 10/19/2007 KEEP
    54613648 D14850A325633754613648 8/30/2012 DELETE
    54613652 D27777245500-90154613652 10/19/2007 KEEP
    54613652 D14850A325633754613652 8/30/2012 DELETE

    The terms I think I need to use are SELECT, HAVING Count(*)>1, CASE, INSERT, GROUP BY and ORDER BY.
    I have no idea about syntax. Need help!

    SELECT TEST.SN, TEST.UI, TEST.REG-DT
    FROM TEST


    WHERE TEST.SN HAVING Count(*)>1
    CASE
    WHEN REG-DT = min_date THEN INSERT TEST.ACTION = KEEP
    WHEN REG-DT > min_date THEN INSERT TEST.ACTION = DELETE
    GROUP BY TEST.SN
    ORDER BY TEST.REG-DT.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    per the other thread, the syntax you are using is Tsql, but you say you are using access. Please clarify

  3. #3
    Treecat is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Sep 2019
    Posts
    4
    I guess that what happens when you cut a paste from Word and Excel - a mess.

    I think i want to use SQL because I cannot figure out how to do this in the Access Design View. I am not familiar with Tsql.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK, so the table is an access table, not a sql server one. It matters because the code will be different

    However I am confused, you say you have three key fields - but your example, with the exception of SN, they are different. So are you saying that a record is considered a duplicate if it has the same SN number? or something else? So far as I can see the UI field is irrelevant.

    You also say
    Usually the record to keep is the oldest of the pair.
    'Usually' implies there is more to the rule than 'the oldest'. Please clarify. Determining the oldest is straightforward, applying further rules will make it difficult

    And two more questions - is there only ever one duplicate (a pair) or can there be more? Can a duplicate exist with the same SN and date values?

    these need to be clarified before making solution suggestions otherwise an awful lot of time will be wasted

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm heading off for the weekend so here is some code that does your report. Assuming your duplicates query is called qryDup and returns the SN, UI, regDT fields

    note that using spaces and non alpha numeric characters in table and field names can cause problems, better not to have them.

    Code:
    SELECT SN, UI, regDT, IIf([regdt]=[minofregdt],"keep","delete") AS Action
    FROM qryDup 
        INNER JOIN (SELECT SN, Min(regDT) AS MinOfregDT
                           FROM qryDup
                           GROUP BY SN)  AS M 
        ON qryDup.SN = M.SN;
    call this query qryRpt

    and to delete (assuming your table is called tblRegistry)

    Code:
    DELETE DISTINCTROW  tblRegistry.*
    FROM  tblRegistry 
        INNER JOIN qryRpt 
        ON tblRegistry.SN = qryRpt.SN AND tblRegistry.regDT = qryRpt.regDT
    WHERE Action='delete'
    this deletes all but the earliest record and assumes SN is the key field, the UI field is only there for the report

  6. #6
    Treecat is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Sep 2019
    Posts
    4
    Quote Originally Posted by Ajax View Post
    I'm heading off for the weekend so here is some code that does your report. Assuming your duplicates query is called qryDup and returns the SN, UI, regDT fields

    note that using spaces and non alpha numeric characters in table and field names can cause problems, better not to have them.

    Code:
    SELECT SN, UI, regDT, IIf([regdt]=[minofregdt],"keep","delete") AS Action
    FROM qryDup 
        INNER JOIN (SELECT SN, Min(regDT) AS MinOfregDT
                           FROM qryDup
                           GROUP BY SN)  AS M 
        ON qryDup.SN = M.SN;
    call this query qryRpt

    and to delete (assuming your table is called tblRegistry)

    Code:
    DELETE DISTINCTROW  tblRegistry.*
    FROM  tblRegistry 
        INNER JOIN qryRpt 
        ON tblRegistry.SN = qryRpt.SN AND tblRegistry.regDT = qryRpt.regDT
    WHERE Action='delete'
    this deletes all but the earliest record and assumes SN is the key field, the UI field is only there for the report
    "Thanks Ajax. I cannot delete records I can only recommend records for deletion. The output still requires eyeballs to make sure that the oldest record is going to be the one to keep. What does that change?"

  7. #7
    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
    The code Ajax provided as QryRpt should do what you want. Since it is a SELECT query, it only reads the data. Just use this code.

    His second code DELETE.... is an action query that would delete records identified in QryRpt. Do Not run this Delete code, since you do NOT want to physically delete any records

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The output still requires eyeballs to make sure that the oldest record is going to be the one to keep. What does that change?
    What Orange said

    If sometimes you don't want to retain the earliest record then you need to convert the qryRpt into a make table, then users will need to change Keep/Delete as required by editing the table. Or perhaps export to Excel and users edit that instead.

    Once done, you can modify the delete query to link to this made table instead of qryRpt

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

Similar Threads

  1. Newbie Here! Please help me to solve this query..
    By EileenAchil in forum Queries
    Replies: 2
    Last Post: 11-24-2016, 12:45 PM
  2. Replies: 11
    Last Post: 04-08-2015, 02:55 PM
  3. newbie: Struggling for query
    By adhossain in forum Access
    Replies: 3
    Last Post: 02-22-2015, 04:47 AM
  4. Newbie Query
    By sordfish555 in forum Access
    Replies: 1
    Last Post: 11-17-2013, 03:49 AM
  5. Newbie Help with Access Query
    By MartyMojito in forum Access
    Replies: 1
    Last Post: 03-17-2011, 02:50 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