Eliminating Duplicate Records

[Paul Fenton]

I have a table of customers where some appear once, others more than
once. Each customer has a product ranking from 1 to 8 in his record.
Looks like this:

Cust Rank
Smith 1
Jones 3
Jones 3
Jones 5

What I want to do is eliminate the duplicate records for Jones and
keep the record with the lowest value in Rank. I want to end up with:

Cust Rank
Smith 1
Jones 3
Jones 3

Can I do this in a query and if so, how?


Paul Fenton
(e-mail address removed)

 

[Paul Fenton]

I should have added that there are other fields that need to be
returned in this query such as Product Price, quantity, etc. This is
a problem using a query which groups records by Cust and Rank.

Paul Fenton