delete all but one of duplicate records

[Guest]

I want to delete all but one of the records that have duplicate values in one
field. In query design I have tried setting unique value to yes but that
leaves me without the other fields I need. I don't know code well, so is
there a simple way to use the unique value method but still show all the
fields? Or actually delete said records in the table?

 

[Guest]

Which of the other fields do you want to keep? This is important and you need
to be very, very sure of what you want.

Provide the table and field names along with some sample data and what you
want to see after the deletion.

 

[Guest]

Unique value field is [Expr1]
Sample fields to be displayed for mail merge purpose:
first name
last name
street number
stree name
etc

My preferance is to have the records deleted in the table but I can make do
with a query solution. Whichever is easiest for a beginner.

 

[Guest]

A different point of referance: Let's say I have ten append queries to the
same mastertable with [Expr1] as the primary key.
I append query one and all 100 records are accepted.

I append query two and one record is rejected because it is a duplicate in
the primary key.

Query three has three duplicates in the query but no duplicate presently in
the mastertable. Will none of these duplicates be appended or will just one
be appended?

 

[John W. Vinson]

I want to delete all but one of the records that have duplicate values in one
field. In query design I have tried setting unique value to yes but that
leaves me without the other fields I need. I don't know code well, so is
there a simple way to use the unique value method but still show all the
fields? Or actually delete said records in the table?

Two suggestions:

Make it a Totals query; Group By the fields that define uniqueness, and select
First as the "totals" operation for the other fields. Don't group by any
autonumber Primary Key fields - that field will be unique within a table, even
if the name and address are duplicates.

Or, create a new, empty table. Create a unique Index on the combination of
fields that identify a unique record. Run an Append query to copy the data
into this table (again, don't include the autonumber field if there is one).
This will load the first occurance of the duplicate, and just discard all the
additional instances. Any associated data will be left in the original source
table but will NOT be copied into the new table.

John W. Vinson [MVP]