[AccessD] super critical:PercentRank function in Access

On 8 Jun 2003 at 19:04, Klos, Susan wrote:

> Has anyone used Worksheet functions in Access?  I am trying to rank a column
> of numbers in a table and I have no idea even how to begin.  HELP!! Please.

How many numbers,  why are you doing - what sort of output do you 
want?

Maybe the easiest solution would be to "roll your own" completely.

Fill an array with the numbers, sort the array, assign a rank to each 
element (grab a count of the array elements first if you want a 
percentrank), write it out to a temporary table or whatever. A 
function like that could be written in a few minutes.

or use the table itself:

Have a "rank" field in the table.
Create a recordset of all the required records sorted by value.
(Get the recordcount if you need percent rank)
Step through the records and update each one with its rank or percent 
rank.

With either approach, you would set up three variables: 
PreviousScore, Rank and Position.
Set Position = 0, PreviousScore = -1 (or some other "impossible" 
value)
Then use something like this:
Do
Position = Position + 1
If  !score <> PreviousScore then Rank = Position
!rank = Rank (or !PercentRank = Rank/Recordcount)
PreviousScore = !score
.movenext
Loop until .eof





-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.