Finding doubles in Access-table

[Comora]

I have a database with about 70000 entries. Lots of them are double and I would like to clean the db from these doubles.
Now, does someone know how to check for double entries in an Access database and how to remove one of them?

[iring]

In Access, you can use the "Wizard double records" to create a query which will show you the double records. Very easy AND very effective !

[Comora]

I tried this wizard, but he just gave me as a result how many doubles of which record are in the db....

[paulw]

Are you after completely duplicated records or duplicated fields?

Use SQL with a Group By clause and the Count function to identify duplicates.

Cheers,

P.

[Comora]

I am looking for duplicate records and I would like to do it over VB, but if you know an easier way.....post it.
Thanks

[paulw]

Do you know SQL at all?

the form would be of:
SELECT (fields), Count(LastField) As DupCount
FROM Table
GROUP BY (fields)
WHERE DupCount > 1

This would give you duplicate records. I have always done this in code and marked only subsequent duplicated records with a flag and then used that flag in a DELETE SQL query.

Break it down into small steps.

OK?

Paul.

[iring]

The wizard works fine with me !!
Using the wizard, you have to select the fields used to determine which records are double or not. After finishing the query, Access shows the double records.
The equivalent SQL of an example I used :
SELECT DISTINCTROW [lidnr], [naam], [voornaam]
FROM AlleDeelnemers
WHERE [lidnr] In (SELECT [lidnr] FROM [AlleDeelnemers] As Tmp GROUP BY [lidnr] HAVING Count(*)>1 )
ORDER BY [lidnr];

[Comora]

No, I don't know SQL! Still learning.
But thanks for the post. I will try my best.
Cheers