Solved: Delete duplicate (identicle) Record!!!

[Van Ieperen]

I've been trying to delete duplicate IDENTICLE records from a table. With the delete query it doesn't work because there is no "WHERE" clause possible. (All fields are identicle)

Can somebody tell me if this is possible with a query and if not, can somebody please tell me how to delete the "current" record in VBA.
(I'll run a nested loop then....): pray2:

[OBP]

The easiest way to delete duplicates using a query is to add a field to the table that is used with an update query to add as many Field's data to it as it takes to create a field that would identify the duplication, or as it going to be used to Identify "Uniqueness".
Then copy the Current Table's Structure only and set the new Field's property to Indexed and No Duplicates.
Now create a Select Query on the Current table that has the duplicates and then change it to an Append Query, where it appends the data to your new Table.
The new table will only accept one record from each set of duplicate records due to the "No Duplicates" setting.

[Van Ieperen]

Thanks a lot, that put me on the right track... :friends:

[stanl]

The easiest way to delete duplicates using a query is to add a field to the table...

Why not just a Make-Table Query with SELECT DISTINCTROW....INTO [temp] ; rename original table table_orig ; rename temp to original table {these last 2 steps can be eliminated once you are sure the query does what you intended}. .02 Stan