Mark duplicated records for deletion

[Gina]

I have some 8000 address records which contain duplicates on the field
postcode (zip code). I can run the Access (2007) query wizard to
identify the dupes but the resultant recordset (of 1400 records)
contains ALL the records which contain a dupe. eg

Postcode
AB10 1UP
AB10 1UP
AB11 6DJ
AB11 6DJ
AB11 6HJ
AB11 6HJ
AB11 6HJ

I want to be able to mark for deletion only 1 AB10 1UP, 1 AB11 6DJ
but 2 AB11 6HJ etc etc. I want to always keep the first instance of
the dupe. I could eyeball the 1400 records and just mark the "bad"
ones but this is an exercise that will have to be repeated.

Any clues how I can achieve this quickly using either queries or code?

Thanks

Gina

 

[John W. Vinson]

I have some 8000 address records which contain duplicates on the field
postcode (zip code). I can run the Access (2007) query wizard to
identify the dupes but the resultant recordset (of 1400 records)
contains ALL the records which contain a dupe. eg

Postcode
AB10 1UP
AB10 1UP
AB11 6DJ
AB11 6DJ
AB11 6HJ
AB11 6HJ
AB11 6HJ

I want to be able to mark for deletion only 1 AB10 1UP, 1 AB11 6DJ
but 2 AB11 6HJ etc etc. I want to always keep the first instance of
the dupe. I could eyeball the 1400 records and just mark the "bad"
ones but this is an exercise that will have to be repeated.

Any clues how I can achieve this quickly using either queries or code?

Thanks

Gina

What do *YOU* mean by the "first"? Tables have no order; they're like a
sackful of records. Is there some Primary Key or other field which can
establish a sequential order to the records? Or does it matter?

If your intent is to create a new table with one record per postcode, you
could create a new table with a postcode field; define a unique Index on the
field; and run an Append query appdending records from this table into it. A
delete query is also quite possible, but I'd need to know what else is in the
table and how you want to deal with the "first" issue to help.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com