Deleting duplicates from a table

[jamesk1979]

I have a table with the following fields;
cus_name
acc_num
acc_branch
cus_fname
cus_sname
etc...

I need a query which will scan through the table and delete any duplicates in the acc_num field (i.e. if there are 6 entries for 123456, I need to keep one and remove the other 5 from the table).

Can anybody help? I don't have any VBA experience so if you could explain the quickest way of doing it using the Access SQL view, that would be great.

Many Thanks.

 

[r937]

which one do you want to keep?

 

[jamesk1979]

Sorry, I need to keep the first instance and delete the remaining ones.

Cheers, r937...

 

[r937]

well, right there is the nub of the problem

how do you define "first"?

is there an autonumber on each row?

is there a date field?

do you want to keep the row with the lowest customer name?

highest account balance?

if there is no other column that will assure that you can select exactly one row out of the duplicates, then the sql to eliminate duplicates will be substantially more complex


rudy

 

[jamesk1979]

Yes, there is an autonumber value on each row.

I want to keep the row with the lowest autonumber value. Is this possible?

Thanks,
James.

 

[r937]

sure, it's possible

set up a Make Table query (SELECT... INTO...) to save only the rows you want to keep (the ones with the lowest autonumber)

then delete the rows where the autonumber is not in the saved data

like this: thread701-686164

rudy

 

[jamesk1979]

Thanks rudy. Much appreciated.