Need help Deleting Dup records but leaving the first

[lquigley]

Thanks guys, I already got an answer from another forum.


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!!!!

[ranman256]

The only way to use a query to delete in another query is the IN subquery.
First, use the Query Wizard and make a FIND DUPLICATEs query, any table.
Now edit the query,
Note the field with the dupes has a sub-query in the critieria. This is what you do but with a delete query.
this subquery will be the one you made above. But it only needs the rec.key as the only field.

[June7]

Bing: Access delete duplicate records same table

http://answers.microsoft.com/en-us/o...b12f16c?auth=1
http://www.bluemoosetech.com/microso...l.php?jid=25&a..