Hows do you do a ranking in a Query?

[crabjoe]

Lets say I have 5 records by name in one field and a number in associated with the name in field 2

Example:

Name Number
John 43
Michelle 5
Jerry 321
Betty 21
Michael 7

What I want to do is build a query where I rank the people by number in decending order but rank them as below.


Rank Name Number
1 Jerry 321
2 John 43
3 Betty 21
4 Michael 7
5 Michelle 5


How do I get the ranking done in the query? I've been doing it by making a table then adding an auto number field, but would rather be able to do this in a query if possible as I can in a report.

TIA

 

[Isadore]

Tia: In the sort window of the Query grid in design view you should pick "descending" on the numeric field and that and that should do it.

 

[crabjoe]

Isadore,

Thanks for the info, but the problem is making the numbers descend. What I'm trying to do is add the "RANK" field in the query and get it to rank. I hoping it's possible to do but I havn't been able to do it within the query.

TIA

 

[PaulF]

You can create a Public Function that uses a Public Variable, and use this Function to do the Ranking. But be careful when you use this, because due to the fact that it uses a Public Variable, if you lose focus on the query, and then regain it, without resetting the Variable back to Zero, it will reincrement. Weird, yes, but then this is Microsoft Access, so why should we be surprised.



Option Compare Database
Option Explicit
Global RecordNum
Function DoesIncrement(AnyValue)
RecordNum = RecordNum + 1
DoesIncrement = RecordNum
End Function


then call this in your query by using an Expression Field and passing a Field Name as the Value for the Function

Rank: DoesIncrement([Name])

and I'd send a command to reset the Variable back to Zero before opening the query as below.

RecordNum = 0
DoCmd.OpenQuery ("QueryName&quot

One additional comment.........your Field names, they are going to cause you problems somewhere down the line: Name is a property and Number is a data type and a Reserved Word used by Access.....I'd change them to something Access doesn't use in its processing.

PaulF

 

[UKRacer]

This is probably a long way round, and others may have a better solution, but it will work for you...

I can't think pf any way round the need for a second table (called, let's say "tblRanked&quot with fields matching those you want to display, and in addition an autonumber field. Let's say you call this field "Rank"

Then when you want to display the info, use the following 3 steps (you can put them all in a macro or code to run whenever you need the info):

1) Use one query to delete all the information in the duplicate table.
2) Use a second query to append into the duplicate table all the records in the appropriate order (ie sort "descending" for the [Number] field.
3) HERE'S THE TRICK! Make a new query to look at the new table, and use the following formula instead of the field "Rank":
[Rank]+1-DMin("Rank","tblRanked&quot

This will always start the ranking at number 1.

This will probably do you until someone else comes up with a cool bit of code to do it all in one for you!

Pete