how to rank results produced by a query

[melvinkent]

hey guys..am supposed to cteate a school system where i have to rank students according to the average of the exams taken. i have already done the average but just dont knw how to do the ranking. my query looks like this...so i need to add a field that will rank the students like. shown below the query below.

name. average.
kent. 546
ian. 657
nick. 562
solomon 678


name. average. rank
kent. 546. 4
ian. 657. 2
nick. 562. 3
solomon. 678. 1

[Rabbit]

You can do this by joining the aggregate query to itself and doing a count.

[NeoPa]

Put another way, when you link the table (or recordset of whichever type) to itself you specify that only those records which are less than (or greater than in some scenarios) are included. The count of such records is always one less than the ordinal number (rank) that you require, so by adding one you get what you need.

[mshmyob]

Because Access does not have a RANK function you have to create your own as suggested.

The following displays the student name, the average and the ranking. In this scenario the student name and averages are stored in a table called tblScores.

You would need to change table names and field names as required.

SELECT tblScores.StudentName, tblScores.StudentAvg,
(SELECT  Count(*) FROM tblScores AS tbl1
WHERE tblScore.StudentAvg < (tbl1.StudentAvg+1)) AS MyRank
FROM tblScore;

cheers,

[zmbd]

Here's a very nice and detailed explanation of what I think you're after:
Ranking or numbering records
Microsoft website also has a few tutorials over this that are easily found via your favorite search engine.