Rank Function

[Jeff]

Hello Everyone

Thanks in advance for any assistance you can give me.

In Excel there is a function called Rank which allows you to rank a value
within a range of values. Is there anything in Access that will do
something similar - either on a Form, Query or Report.

I am building a database that will store Dr Productivity information for
each month

There will be Dr information from about 18 different centers
There will be about 7 main data points for each Dr etnry for a month
I will be performing calculations on these primary data points and I want to
be able to rank on one of the calculated fields
I need to be able to rank ALL of the entries and get a total ranking and
then be able to send reports to the various centers including the overall Dr
ranking from ALL of the centers.

So my main question is - is there a Rank function or formula within Access
as I cant seem to find anything in help. I know there has to be a way to do
this and I cant think of one at the moment.

Please let me know if you need any more details on my project.

Thanks again for your help.

Jeff

 

[dbahooker]

all you need to do is sort these; and then count the number of records
that are higher than the item

so if you have 20 items; and you sort them by date

you say

Select (select count(*) from myTable subQ Where subQ.DateEntered >
myTable.DateEntered) as Rank, DateEntered, FirstName, ProjectStatus
FROM MyTable

it would really depend on what tables, fields you had.. and at what
GRAIN you want to count at

 

[Ron Hinds]

You could do something similar with a recordset. Where are you going to
store the returned values? In another table, or the same one? In any case,
here is how you would determine the rank.

Dim db As Database
Dim rs As Recordset
Dim I As Integer

Set db = DBEngine(0)(0)

Set rs = db.OpenRecordset("SELECT * FROM your_table_name ORDER BY
your_rank_field")

I = 1

Do Until rs.EOF
MsgBox rs!doctors_name_field & " is ranked " & I

I = I + 1
rs.MoveNext
Loop

Add ASC (ascending) after "your_rank_field" if you want it ranked from
smallest to largest.

 

[Steve Schapel]

Jeff,

The general concept here is to use a calculated field like this in a
query...

Rank: DCount("*","YourQuery","[Score]>=" & [Score])

.... where 'YourQuery' is the name of the query where you calculate the
overall 'Score' which is to be the basis of the ranking,