Duplicate records delete

[Berti]

On the below data I need to delete records where dep is blank or the same for the same ID. So I need to find and del recNr. 1,4,5. Any help will be appreceated!Thanks

recNr ID Dep
1 ABX
2 ABX 1234
3 ABX 1258
4 CDE
5 CDE 7895
6 CDE 7895

 

[rainman89]

you can do a delete query where the criteria is null for the Department

and you can also create a duplicates query to help you identify the duplicates

 

[gear]

I have the same issue as Berti's. I created a find duplicate query through wizard. It identifies the duplicates. When I converted the same query to a Delete query, it deletes all the records. For example, it displays the two duplicates records
ABC 7745
ABC 7745
When converted to delete query, it deletes both the records. I want to retain one record and delete the duplicate one. Shall be grateful for help.

 

[Kempes]

Gear,

you need to add a unique identifier to the table.
Then you can choose to keep either the min or max value of this new field (no difference either way) to give you the remaining single record.

 

[gear]

Thanks for guidance. I will do it.

 

[Pat Hartman]

Create an empty table with the two columns defined as the compound primary key. Create a query that appends the recordset with the duplicates to the new table. You will get an error message that Accesss has failed to add blah, blah. Ignore the error. Your new table will now contain only unique rows based on the two column primary key.