Rank by State ?

[Matt Zimmerman]

Hey all, I have spent a few hours researching this to no avail.

I am trying to take a table and rank my values by state.

Table has ID#, State, Points

I want to select all fields, and rank each id within a state by
points. There are roughly 50 records per state.

Please help!

 

[Access Developer]

Do you want to do something more than show the ID#s in order by ascending or
descending points, within each state?

Here's some SQL that will return them in order by state, and highest points
to lowest points within State. Two minutes with the Query designer, and copy
from SQL view:

SELECT tblPointsEarned.State, tblPointsEarned.Points, tblPointsEarned.[ID#]
FROM tblPointsEarned
ORDER BY tblPointsEarned.State, tblPointsEarned.Points DESC;

If I wanted ranking numbers added, I'd read using this query from VBA DAO
code, and fill in a Field called "Rank" with sequential numbers within
state. With a little cleverness, i don't see why you couldn't do it in a
Query using the DMAX domain aggregate function, if you'd prefer that. What
is it you want to do with these values once you have them?