How To Delete Duplicate Records from Table

[gorgon777]

I have a table of records which have duplicate values in a certain column. e.g

ID      Name       Colour    Rate
1        John       Black       8
2        Peter      Black       9
3        Anne       Blue        8
4        Polly      Yellow      8
5        Serah      Green       9
6        Gary       Yellow      9

What I would like to do is keep unique Colour values only. So in the above example, there must be only one person who has Black, and only one person who has Yellow. I want the person that is removed to be the one who has the higher rate. So those with 9 will be deleted.

How can I do this in Access via SQL?

 

[MStef]

Look at "DemoRemoveA2000.mdb" (attachment, zip).
1) Create a "Table2New" (look ta INDEXED PROPERTIES on the field Colour1).
2) Create an Append query (look at qryTable1Append).
3) Run Append query and look at a Table2New.
I think it can help you. Adapt it in your MDB.

My fault:
In Append Query delete sort ascending on ID, and put
sort Ascending on Colour and on Rate.

 

[Severin]

you can also create a couple of queries:

Query1:
SELECT TableName.Colour, Min(TableName.Rate) AS MinOfRate
FROM TableName
GROUP BY TableName.Colour;

Query2:
DELETE TableName.*, Query1.Colour, Query1.MinOfRate
FROM TableName LEFT JOIN Query1 ON (TableName.Rate = Query1.MinOfRate) AND (TableName.Colour = Query1.Colour)
WHERE (((Query1.Colour) Is Null) AND ((Query1.MinOfRate) Is Null));

Just exchange TableName with the name of your table!~)

 

[gorgon777]

Thank you that worked perfectly!