[AccessD] Eliminating duplicate rows

Thanks to you all!  Gustav ( and Bill!) get the Guernsey as further
investigations revealed that we want to keep the last (latest added)
record.  So by changing First to Max (surrogate PK - but of course! At
least I got that right) this is a sweet and elegant way of removing the
dups.

Susan, I think your method will delete all records that are duplicates.

Moral lesson learnt: Carving fixes at 2:00am is not a good idea if you
cant even tell the difference between Insert and Update.

Thanks to all
Bruce

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, June 13, 2003 8:09 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Eliminating duplicate rows


Hi Bruce

> Due to a (ahem) programming feature, I now have a table with several 
> thousand rows of which hundreds are redundant.  I am loking for a way 
> to delete the redundant rows.

You may use an idea I received once from Bill (William) Mitchell for a
similar job (is he still with us?). The trick is the use of "Not In"
which frees you from locking the main
table: 

<quote>

Make sure to make a backup & test it first. . .

1. Add an autonumber field if you don't have a PK.
2. Create a totals Query1 which shows GroupBy the name, and First of the
PK. 3. Create a delete Query2 with the PK criteria like this:

Not In (Select FirstOfPK From Query1;)

When you run the delete Query2 it will delete everything _except_ the
first occurrence of each name.

</quote>

Have fun.

/gustav

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com