none
Need help deleting duplicate records but keeping the first RRS feed

  • Question

  • I am new to access.  Using Access 2010.  I am working trying to delete duplicate records from a table.
    I have set a unique key to the table [tbl_Cmbn_RateSheetTerms_Audit_Collapsed] in order to identify duplicate data within the table for the field [RATESHEETCODE]
    I then created a query [qry_000e_list_of_firsts_RS_collapsed] to identify the first record of the duplicates here is the SQL: SELECT First(tbl_Cmbn_RateSheetTerms_Audit_Collapsed.Uniq  ueKey) AS FirstOfUniqueKey, tbl_Cmbn_RateSheetTerms_Audit_Collapsed.RATESHEETC  ODE FROM tbl_Cmbn_RateSheetTerms_Audit_Collapsed GROUP BY tbl_Cmbn_RateSheetTerms_Audit_Collapsed.RATESHEETC  ODE;

    I then created a query [qry_000f_list_to_delete_RS_collapsed] to identify the records that need to be deleted.  I used my original table tbl_Cmbn_RateSheetTerms_Audit_Collapsed and the query qry_000e_list_of_firsts_RS_collapsed linked them by unique key here is the SQL: SELECT tbl_Cmbn_RateSheetTerms_Audit_Collapsed.UniqueKey, tbl_Cmbn_RateSheetTerms_Audit_Collapsed.ReportMont  hYr, tbl_Cmbn_RateSheetTerms_Audit_Collapsed.RATESHEETC  ODE
    FROM tbl_Cmbn_RateSheetTerms_Audit_Collapsed LEFT JOIN qry_000e_list_of_firsts_RS_collapsed ON tbl_Cmbn_RateSheetTerms_Audit_Collapsed.[UniqueKey] = qry_000e_list_of_firsts_RS_collapsed.[FirstOfUniqueKey]
    WHERE (((qry_000e_list_of_firsts_RS_collapsed.FirstOfUni  queKey) Is Null));

    I am now trying to create a third and final query to delete the duplicates from the original table.  I was attempting to do this using dlookup but I can't figure it out. HELP PLEASE!!!!
    Wednesday, September 16, 2015 3:39 PM

Answers

  • Hi. Make sure you have a backup copy of your db before trying this out:

    DELETE FROM tbl_Cmbn_RateSheetTerms_Audit_Collapsed WHERE [Uniq ueKey] Not In(SELECT FirstOfUniqueKey FROM qry_000e_list_of_firsts_RS_collapsed)

    untested - Hope that helps...

    • Marked as answer by lquigley Wednesday, September 16, 2015 4:24 PM
    Wednesday, September 16, 2015 3:59 PM

All replies

  • Hi. Make sure you have a backup copy of your db before trying this out:

    DELETE FROM tbl_Cmbn_RateSheetTerms_Audit_Collapsed WHERE [Uniq ueKey] Not In(SELECT FirstOfUniqueKey FROM qry_000e_list_of_firsts_RS_collapsed)

    untested - Hope that helps...

    • Marked as answer by lquigley Wednesday, September 16, 2015 4:24 PM
    Wednesday, September 16, 2015 3:59 PM
  • In general terms: 'first' is a relative term.  So one presumes there is a numerical sequence or date field that distinctly identifies it.

    This is addressed in steps:

    1. Find Dupes - there is a preset query wizard in the product for duplicates you can use, or refer to as a model - - save that query as 'TheDupes'

    2. Find 'First' - do an aggregate query (Sigma icon looks like a big E) grouping on the common field in The Dupes and use either Max or Min to identify the first of the duplicates - - save query as 'TheFirsts'

    3. One cannot apply updateable action queries using an Aggregate query - so next use a Make Table query based on TheFirsts - to write the IDs to a temp table.

    4. One presumes you wouldn't delete without saving a copy of original table - so in this case rather than delete the non-firsts it will be easier to write the Firsts to a new table - and then use this table going forward.  Copy the structure of the table as a new table without data - then make a query with a join of your temp table to the existing data table - this will result in the The Firsts records only - and set up an Append query to write to your new blank table.

    Wednesday, September 16, 2015 4:00 PM
  • Thank you so much!!! You are a genius and saved me from a lot of hair pulling,

    I really appreciate your time and effort in answering my question.  Once I read your SQL I understand and I will be able to use this logic with other reports that I need to import in Access.

    Thanks again :)

    Wednesday, September 16, 2015 4:32 PM
  • Thank you for taking the time to respond.
    Wednesday, September 16, 2015 4:33 PM
  • Thank you so much!!! You are a genius and saved me from a lot of hair pulling,

    I really appreciate your time and effort in answering my question.  Once I read your SQL I understand and I will be able to use this logic with other reports that I need to import in Access.

    Thanks again :)


    Hi. You're very welcome. We're all happy to assist. Hope it works out for you. Good luck with your project.
    Wednesday, September 16, 2015 4:41 PM