Delete all duplicates except one

[Tom123]

Hello,



I want to make a query which finds all duplicated data and deletes all but one of each of the records. For example:

Steel
Zinc
Aluminium
Aluminium
Zinc
Copper
Zinc

Would become:

Steel
Zinc
Aluminium
Copper



I used the find duplicates wizard and turned it into a delete query but it deleted everything that was duplicated. For the example above it would have left only Steel and Copper. How do I make it leave one instance of each?



Cheers,

Tom

[JoeM]

This looks like a follow-up to the previous question I helped you on.
Let's take a step back for a moment. Is it really necessary to do all these actions queries to add, delete, or append records?

The reason I ask is because if you are just looking for a listing with no duplicates, you can easily get that from a Select Query without have to do any actions queries.
You can either create an Aggregate Query to "Group" like records together, or you can create a Select Query that has the duplicates, switch to SQL View, and add the word "DISTINCT" after "SELECT". Both of these things will hide all duplicates.

Whether or not you really need to delete table records or not kind of depends on what you ultimately want to do with this information.

[NTC]

there is actually no way to do what you seek without a second field/column as a point of reference to designate which stays and which deletes.

consider an Aggregate query, rather than the Find Duplicates; which with grouping will result in 1 of each. What you do next however is unclear.....

[orange]

Tom,
As the others have said - we don't know What you really are trying to do. You seem to be jumping through hoops just to get a Distinct list.
Tell us where the data is coming from. There are ways to Not Allow duplicates, but we need to know more about your requirements.

[Tom123]

I am trying to make an inventory database where one of the tables is just a list of what was purchased. I have number of queries that total all the repeat instances of a single part number to give the total number of each part that was purchased. However, I am having problems with part descriptions (which can be equivalent but still different) and are messing up these queries and counting them as different parts. What I want to do is have a table relating part numbers to descriptions but I want only one description per part number because they should be equivalent.

Basically I want to make a table of all part numbers and their descriptions and then I want to find duplicates of the part number in that table and delete all but one of each. Then I can relate quantities of part numbers to descriptions of part numbers.

[orange]

Please show us all the fields in your Table and all the tables in your database.
I find your post confusing -- lacks context.