Deleting duplicate composite keys

[SamirNaenenjad]

I need to make a composite key in a table, and of course I can't do that until I have deleted the duplicate keys already there. Is there any way to do this with an SQL statement? I can select the records to delete with:

SELECT [Account_ID] & [CowID] FROM tblCowEvaluation
GROUP BY [Account_ID] & [CowID]
HAVING COUNT([Account_ID] & [CowID]) > 1

but when I try

DELETE * FROM tblCowEvaluation
GROUP BY [Account_ID] & [CowID]
HAVING COUNT([Account_ID] & [CowID]) > 1

the DELETE * won't jive with the grouping. Any thoughts? Is there a different approach I can take?

Thanks for any help,
A.J.

 

[oharab]

DELETE * FROM tblCowEvaluation WHERE [Account_ID] & [CowID] IN (SELECT [Account_ID] & [CowID] FROM tblCowEvaluation GROUP BY [Account_ID] & [CowID] HAVING COUNT([Account_ID] & [CowID]) > 1;

Should do it. You can't edit a group query, so you have to use the group query as criteria for a delete query.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[THoey]

Another way to do this would be to create a duplicate table without the data, set your keys, and then copy the data to it with an append query. The dupe's will not get loaded. Then you can delete the original table and rename the new, clean table... This is a good way if it is a one time thing. If you have to do this frequently, I would go with the query... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...