Keeping only one record with duplicates of a field

[Guest]

I have a table with a field that has duplicates in a certain field. I want
to create a new table with all fields from 1st table but only one record for
those with duplicates in this certain field. It doesn't matter which record
is kept of those that are duplicates. Does anyone have the SQL code to do
this?
Thanks,
Don

 

[John Spencer]

Try the following

SELECT YourTable.FieldDupe,
First(YourTable.FieldA) as FieldA,
First(YourTable.FieldA) as FieldB,
First(YourTable.FieldA) as FieldC
FROM YourTable
GROUP BY YourTable.FieldDupe

You can use that as the source of an append query if you really need a new
table.

 

[Guest]

Thanks John! It works fine. I used the INTO reserved word at the end of the
SELECT to name the table I wanted this query to write to.
Don