Calculating places from scores

[martie]

I have a db with classes of competitors (comps pulled from another db). One layout allows the user to enter the rawscore earned by each competitor in a particular class. Another field calculates the percentage from the rawscore (determined by the max points of the test done in the class - pulled from another db).

I need a field to determine placings - the highest percentage gets 1, next highest 2, and so on to 6th place. No placing after 6th. If the rawscore is entered as 'SC', 'HC', 'NS' or 'E' those letters should appear in the placings field.

I can't figure a calculation to do this - help?

TIA,

Martie

[Razumovsky]

I cant think of a way to do it without at least sorting first.

If you sort by percentage from highest to lowest, then you could have a calc field:

case(

status(currentrecordnumber)=1, "First Place " & RawScore,

status(currentrecordnumber)=2, "Second Place " & RawScore,

status(currentrecordnumber)=3, "Third Place " & RawScore,...)

Problems with this are that placement would change once sort was changed, and that there would be no "ties" if competitors got the same scores. I think it would be best to script this out and not rely on calc fields.

Also, what does 'SC', 'HC', 'NS' and 'E' stand for?

HTH

-Raz

[martie]

I wasn't sure about scripting it - the thing is that I want the places to "stay" - so that when I have all the classes I can sort by places & export to Excel.

HC - means H'ors Concors - competing for honors (no placing, no ribbons)

NS - means No Show

SC - means Scratch (like NS)

E - means Eliminated

I've been using this db myself & just entering the data - which is not that big of a deal. But, I am now sending it off for someone else to use, so I am trying to make it as 'streamlined' as possible. Also, if the db calculates percentages & placings, it acts as a check for the scorers who do this on calculators.

Thanks for the calc - it may work for my purposes, I will play with it. If I were to write a script, tho' - ^_^

[Razumovsky]

Okay- definitely use a script. I am not sure how the status function exports, and you need to trap the results, not calculate them dynamically.

create 2 global number fields:

g_Percentage

g_Place

1 number field:

n_Place

and your calc field which determines the percentage:

c_Percentage

Script would be:

sort (restore) [this will store the last sort done, which should be by percentage from highest to lowest]

go to rec/req first

set field [g_Percentage, c_percentage]

set field [g_place, 1]

set field [n_place, g_place]

loop

go to rec/req next (exit after last)

if [g_percentage<>c_percentage]

set field [g_place, g_place + 1]

set field [g_percentage, c_percentage]

end if

set field [n_place, g_Place]

exit loop if [g_place = 6]

end loop

This will rank the first 6 competitors from 1-6 in the n_place field. It will match the percentage with a stored percentage (g_percentage)from the previous competitor to allow for ties- that is, given people and percentages:

A 96

B 95

C 95

D 92

E 90

F 90

G 87

H 78

I 67

J 66

your ranking will be

A 1

B 2

C 2

D 3

E 4

F 4

G 5

H 6

I (none)

J (none)

HTH

-Raz

[martie]

Thank you VERY much - it sounds perfect. Sorry it's taken me a couple of days to get back to this...

m