I have an Access-db with approx 5000 records. Many of these records are double and I want to get rid of them. One of those records will have to stay. How can I do that?

Id code opleidingsnaam
1 4406 Accountancy
2 4406 Accountancy
3 4406 Accountancy

delete from yourtable
where id not in
( select min(id) from yourtable
group by code )

make a backup copy first, just in case (and also to compare to afterwards, to convince yourself it worked)

rudy
http://rudy.ca/

It does not work. What went wrong?

please show the sql, and explain how it didn't work

thanks

rudy

Please try it :

Delete from <Table Name> where count(code) > 1

Hope that u will get rid of duplicates.
Thanks.

where count(code)>1 is not valid syntax

eve, did you try the query i gave you?


rudy

Try the following:

delete from table1 as a
where id not in (select min(id) from table1 as b where a.code= b.code)

should be HAVING count(code)>1
but it deletes without leaving one

the query rnealejr sent works for me; tx!