duplicate query problem

[Frank]

I have a database with many duplicate records. What I want to do is as
follows:

find all duplicates records that have identical numbers in the field [kit
number]. I ran a duplicate query for this.

Within the query, I want to keep one of each duplicate record; hence, if I
have 1000 duplicates, I should wind up with 500 records - i.e.. delete only
one of each duplicate record.

What is best way to do this?
Thanks tons for your professional assistance.

 

[Michel Walsh]

Hi,


Don't delete.

Rebuilt a new table not allowing duplicated values on the said field, then,
append the old records into the new table. Some records won't be appended,
that is ok, that is what you want.

Alternatively, make a query like:


SELECT kitNumber, LAST(f1), LAST(f2), LAST(f3), ..., LAST(fn)
FROM myTable
GROUP BY kitNumber



where f1 to fn are the other fields of your table. That query picks just one
record, for each different value of kitNumber. You can then use that query
where you want no dup, or append these records to another table, as
mentioned previously, but this time, no error would occur since there is no
dup, in this query.


Hoping it may help,
Vanderghast, Access MVP

 

[Frank]

Michael:

Thanks so much for your timely response. Will do as you suggested. Sure it
will work.

Once again, thanks a ton for help.