Eliminate duplicate rows based on a single field

[gbaker]

In Access, I have a QUERY "QA" that returns rows with several fields. A particular field "FA" in QA can have duplicate values across many rows. I need to eliminate duplicate rows based on the value of "FA" only. I just want exactly one representative row for any particular value of "FA". My understanding is that the DISTINCT qualifier only filters based on the fields being selected and DISTINCT ROW uses the entire row. This seems like a simple thing but I'm stumped.

 

[sdk]

Hi,

You might be able to achieve this a number of ways, but my first choice would be make the query a "group by" query, group by all the fields in the query except one (other than FA) which you know(?) will be populated, and use an aggregate function: first, max, min.

This is rough & dirty, but might meet your immediate needs.

Cheers

 

[peini]

What I'll do is
1. make a query that select the distinct records based
on the field you want
2. then use make-table query to make the new table which
contains those distinct records
3. delete the old records
4. append the new distinct data