Duplicate record

M

moi

Hello All,
I have a table of two columns (Name & Number) that is sorted in ascending
order on the NAME column and descending order on the NUMBER column, example
below ;

Name Number
Fred 2
Fred 1
Dave 1
Bill 2
Steve 3
Steve 2
Steve 1

I want to run a query that will return ONLY the top record of each name,
example results below

Name Number
Fred 2
Dave 1
Bill 2
Steve 3


Is this possible ??

Any info would be very appreciated
Regards
KB
 
A

Allen Browne

1. In query design view, depress the Total button on the toolbar.
Access adds a Total row to the grid.

2. Under your Name field, accept Group By in the Total row.

3. Under your Number field, choose Max in the Total row.

BTW, if you really have a field called "Name" it will cause you grief. For
example, when you use a form, Access may get confused between the name of
the form and the contents of the Name field. When creating a field, check
that the name you want to use is not in this list:
http://allenbrowne.com/AppIssueBadWord.html
 
Top