Remove records with same values in two fields

[Niebotel]

Let me first explain the background; my application is running on a sharepoint so it is rather slow with "large" tables.
Visistors of our sailingclub are booked into the system and we use data of the visitors if the visitor was here before.
Some visitors come up to ten times a year; they have then 10 records with the same name and boatname but of course with different other data.

Now I want to make the table smaller by removing all the extra records; so the response of the sharepoint improves a little. I have now up to 5000 records (for access no problem, but for the sharepoint....)

So my table (it has also a unique id number) is called "Passanten" and the field which are equal are: "Naam" and "Naam Boot"

Now I want to remove all the records with have same "Naam" and "Naam boot" on one occurce left offcourse:

So an example:

1 xxx 12345
2 yyy 2345
3 zzz 0000
4 xxx 456jj
5 xxx 12345 to be removed
6 xxx 12345 to be removed
7 xxx hftre
8 yyy 2345 to be romoved

In this example I only want to remove the records which are indicated or others as long 1 record of a set is staying

What is the easiest way?

 

[PHV]

Something like this ?

DELETE *
FROM Passanten
WHERE [unique id number] Not In (SELECT Min([unique id number]) FROM Passanten GROUP BY Naam,[Naam boot])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Niebotel]

Thanks a lot; so easy if you know how...
My new problem now is that the numbers of these records show all kind of holes (of course)
I would like to renumber them starting from 1 and increase each record with 1.
Not on the unique primery field ofcourse but in my own defined field in the record called ID (Numeric)
I hope you don't mind that I ask for two answers

Greetings from Willem from The Netherlands