Selecting 1 row with a duplicate second field

[dougmcc1]

Hi,

I have a table with two fields and thousands of rows. In some of these rows, the second field has a duplicate. Like the following:

a 1
b 1
c 1
d 2
e 3

How would I write a query that returns all unique rows and just one of the rows that has a duplicate second field? Such as:

a 1
d 2
e 3

Or:

b 1
d 2
e 3

Or:

c 1
d 2
e 3

Any one of these 3 outputs would work. Thanks.

 

[Jon K]

You can do it with a Totals Query.

In query Design View, drag the two fields to two columns. Click on the Totals button on the toolbar so that a Total row appears in the query grid.

For the first field, change Group By to either First, Last, Min or Max.
.