Results 1 to 2 of 2

Query Newbie

  1. #1
    Treecat is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Sep 2019
    Posts
    3

    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.<br>
    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.<br>
    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.<br>
    Sample report:<br>
    SN UI REG-DT ACTION<table style="width: 494px" class="wysiwyg_dashes" width="494"><tbody><tr valign="top" class="wysiwyg_dashes_tr"><td class="wysiwyg_dashes_td">54613648</td><td class="wysiwyg_dashes_td">D27777245500-90154613648</td><td class="wysiwyg_dashes_td">10/19/2007</td><td class="wysiwyg_dashes_td">KEEP</td></tr><tr valign="top" class="wysiwyg_dashes_tr"><td class="wysiwyg_dashes_td">54613648</td><td class="wysiwyg_dashes_td">D14850A325633754613648</td><td class="wysiwyg_dashes_td">8/30/2012</td><td class="wysiwyg_dashes_td">DELETE</td></tr><tr valign="top" class="wysiwyg_dashes_tr"><td class="wysiwyg_dashes_td">54613652</td><td class="wysiwyg_dashes_td">D27777245500-90154613652</td><td class="wysiwyg_dashes_td">10/19/2007</td><td class="wysiwyg_dashes_td">KEEP</td></tr><tr valign="top" class="wysiwyg_dashes_tr"><td class="wysiwyg_dashes_td">54613652</td><td class="wysiwyg_dashes_td">D14850A325633754613652</td><td class="wysiwyg_dashes_td">8/30/2012</td><td class="wysiwyg_dashes_td">DELETE</td></tr></tbody></table><br>
    The terms I think I need to use are SELECT, HAVING Count(*)&gt;1, CASE, INSERT, GROUP BY and ORDER BY.<br>
    I have no idea about syntax. Need help!<br>
    <br>
    SELECT TEST.SN, TEST.UI, TEST.REG-DT<br>
    FROM TEST<br>
    WHERE TEST.SN HAVING Count(*)&gt;1<br>
    CASE<br>
    WHEN REG-DT = min_date THEN INSERT TEST.ACTION = KEEP<br>
    WHEN REG-DT &gt; min_date THEN INSERT TEST.ACTION = DELETE<br>
    GROUP BY TEST.SN<br>
    ORDER BY TEST.REG-DT.

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,332
    your post is littered with html code making it very difficult to understand.

    Not sure why this is but perhaps try again to take these out

    Also you are using CASE which is a sql server keyword so please clarify what app you are using for the table

    Edit: just seen what looks like the corrected post here https://www.accessforums.net/showthread.php?t=78264

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

Similar Threads

  1. Query Newbie
    By Treecat in forum Queries
    Replies: 4
    Last Post: 09-13-2019, 06:17 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
  •  
Tech Forums: Microsoft Office Forums