Ranking

  • Thread starter Thread starter Richardson
  • Start date Start date
R

Richardson

I have a query with a member listing. They have all previously been
assigned scores which are integer values ranging from 1-1200 but they are
not sesquential and there is not pattern to how they are spaced in value.
Field name Score
Is there a formula I can use to have the query sort the member ascending by
score and then add a new column that is the rank. Rank would need to be
assign as 1,2,3,etc with no spaces and all integer values?
I really appreciate any help you can offer.

Lori
 
Hi,



SELECT a.f1, LAST(a.f2), LAST(a.f3), COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.score >= b.score
GROUP BY a.f1



where f1 is probably the primary key, f2, f3, .. are all the other fields
you wish to see.


Hoping it may help,
Vanderghast, Access MVP
 
Hi,


... use :

ON a.score <= b.score


to get the reverse ordering... evidently.



Vanderghast, Access MVP
 
Following, I've pasted an answer in microsoft.public.access today that could
meet what you are looking for:

Hope being helpful

Tonín



===========================
 
Back
Top