Query on multiple keys - only want top value of second key

T

timorrill

I have a table that looks like this:

ClientID RevisionNum
---------------------------------
10000 1
10000 2
10000 3
10000 4
20000 1
20000 2
30000 1
30000 2
30000 3
30000 4
40000 1
40000 2
40000 3

I would like to great a query that will return the following records:

ClientID RevisionNum
---------------------------------
10000 4
20000 2
30000 4
40000 3

That is, I only want the record with the top value of RevisionNum.

Any suggestions?
 
J

John W. Vinson

That is, I only want the record with the top value of RevisionNum.
Click to expand...

If these are the only two fields in the table (or the only two you want to
see), create a Totals query. Create a new query based on the table; select
these two fields; make it a Totals query by clicking the Greek Sigma icon
(looks like a sideways M); leave the default Group By on ClientID and change
the Totals operator to Max on RevisionNum.

If there are *other* fields and you want to see those fields for the highest
revision number, take this totals query and Join it to your table, joining by
ClientID and RevisionNumber.

John W. Vinson [MVP]
 
Top