Ranking

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



"Richardson" wrote in message
om...
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,


... use :

ON a.score = b.score


to get the reverse ordering... evidently.



Vanderghast, Access MVP



"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
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



"Richardson" wrote in message
om...
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

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

Hope being helpful

Tonín



===========================

I need this to assign ranking numbers to people based on performance. I
had
been using a piece of code which added an autonumber to a newly created
table
but hadn't been able to get it to work in versions of Access after 97, so
this is great, thanks. It also works descending by changing to and
will
work as a make table query.



"Tonín" wrote:

If you really, really need it. And if you don't need an "updatable" query,
and assuming your "Name" field is unique ... then you can use next query:

SELECT YourTable.NameField, YourTable.AddressField, (SELECT
Count(YourTable_1.NameField) FROM YourTable AS YourTable_1 WHERE
YourTable_1.NameField=YourTable.NameField) AS RecordID
FROM YourTable;

BTW, I changed Name into NameField and Address into AddressField.


Tonín



"Don" escribió en el mensaje
...
Is there way to use Auto Number in a query?

For example query returns

Name Address Record ID
A 100 1
B 500 2

I want to add a field that keeps count.

I hope this makes sense.
Thanks