eliminate recordes w/ duplicates in one column

[Guest]

I have a table of records and one column has some duplicates. The entire
record is not a duplicate just some of the values in one column.

how could i eliminate records that have duplicates in the one column. I
con't care which one, the first occurence would be fine.

 

[[MVP] S.Clark]

First, you can find the duplicate using the following from this tip:

http://www.fmsinc.com/free/newtips/sql/sqltip8.asp

After you determine the ones to delete, then you can delete them.

 

[John Vinson]

I have a table of records and one column has some duplicates. The entire
record is not a duplicate just some of the values in one column.

how could i eliminate records that have duplicates in the one column. I
con't care which one, the first occurence would be fine.

The simplest way is to create a new table by copying and pasting this
table, design mode only; put a unique Index on this field; and run an
Append query to move the data into the new table. You'll get a warning
message that "x records were not appended due to key violations" -
these are the dups you don't want.

If you then delete the old table, you'll not have the problem in the
future, since you won't be able to create duplicate records in the
first place!

John W. Vinson[MVP]

 

[Guest]

I know how to find the duplicates. What i want is to not have to delete
each one by hand. There are thousands of them. I just want to pull out the
records that have a unique value in this one field.

I can't use select distinct row, because the entire row is not a duplicate,
just this one column. I don't care which record(of the ones that have a dup
in this column) is pulled, I just need one record from all the ones that
match in this one field.

 

[Guest]

Thanks John

 

[Guest]

I tried your method to find and delete the duplicate records but it returns
the same info as in the original table. I copied the original table -
structure only. I then went into the table design mode and set the one field
as thePrimary Key field, no duplicares. I then ran an append query and all
37,000+ records were appended to the new table. I followed what the ACCESS
Help page said as well as your comments, JV. Did I miss something?