Remove duplicate records (Access 2002)

[sporledm]

Is there a way to remove duplicates when there are two duplicate value fields. We are trying to remove students from a database that have taken the same test twice. We know how to find the duplicates but we want to be able to have a query to remove the duplicates without having to do it manually.

[HansV]

Welcome to Woody's Lounge!

Are the records complete duplicates, or is there a field on which you can distinguish records, an AutoNumber field for example? If the latter is true, you can create a query based on the duplicates query that selects the minimum value (for example) of the distinguishing field for each duplicate, and delete those records.

[siflaar]

Hello Hans,

Great solution. I understand it, but have no clue how to change the SQL so it will work.
As example here the SQL of the duplicate query:

SELECT tblTekstBestand.controle, tblTekstBestand.nummer, tblTekstBestand.[Type profiel code], tblTekstBestand.[Type profiel oms], tblTekstBestand.Peildatum, tblTekstBestand.[Type relatiegroep code], tblTekstBestand.[Code relatiegroep], tblTekstBestand.[Intern specialisme code]
FROM tblTekstBestand
WHERE (((tblTekstBestand.controle) In (SELECT [controle] FROM [tblTekstBestand] As Tmp GROUP BY [controle] HAVING Count(*)>1 )))
ORDER BY tblTekstBestand.controle;

the field [nummer] has the autonumber values.

Could you please explane what to change to only show the lowest numbers of the duplicates?

Thanks in advance.
Patrick Schouten
The Netherlands

[HansV]

The query

SELECT Min(nummer) FROM tblTekstBestand GROUP BY controle

will select the lowest nummer for each controle (not only the duplicates)

The following delete query will remove duplicate records, leaving the one with the lowest nummer value. Create a backup of the table and/or the database before trying it!

DELETE tblTekstBestand.nummer
FROM tblTekstBestand
WHERE tblTekstBestand.nummer Not In (SELECT Min(nummer) FROM tblTekstBestand GROUP BY controle)

[patt]

I found this example in a book Beginning SQL that also does the trick.

DELETE FROM FilmStars
WHERE StarName IN (SELECT StarName FROM FilmStars as S2 WHERE S2.StarId < FilmStars.StarId);

Just change the names to do the same thing. I dont know if this will execute quicker or not?

[HansV]

You probably won't notice much difference with small to medium-sized tables. You'd have to test on a table with tens of thousands of records or more to find out whether one method is more efficient than the other.

In general, though, queries using Not In (...) are relatively slow.