Determining placing values in a list of scores

[larsenal]

I'm hoping someone can point me in the right direction. I'm relatively new to FM and am having trouble with this task. I've tried searching but maybe I'm not using the correct criteria.

I've volunteered to set up a database to track all the results for a 4H dog clubs event results. I have a number of tables that are all working great except when I try to determine the kids placement in their class as a calculated un-stored field. This same task is simple to do in a list layout by grouping and sorting but I would really like to have their placement (1st, 2nd, 3rd etc.) show up in a calculated field that I can see and print out on labels etc. It becomes quite difficult due to the number of variables.

To get my results on a sorted list I must group and sort the following. The specific event (ie. Kittitas Co. dog show), The specific class (apprx. 40 different choices of classes agility, showmanship, obedience etc.), The age group (jr., Int., Sr.), The child's actual score and, in the case of dog agility, time.

I'd like to figure out the way to do this myself but that might take me a long time. If someone could at least point me in a direction I'd appreciate it. The 4H club has grown so much they could really use this soon.

If there is an easy way to do this using the grouping and sorting features in a script or calculation and then assign the placement values that would be great. Any help would be very cool!

Thanks

[comment]

Usually this is done by defining a self-join relationship matching the event (and age group, etc.) and comparing the results, e.g.:

Scores::EventID = Scores 2::EventID

AND

Scores::AgeGroup = Scores 2::AgeGroup

AND

Scores::Score < Scores 2::Score

The rank is then given by a calculation field =

Count ( Scores 2::ScoreID ) + 1

[larsenal]

I see where your going with this. After I thought about it it made sense, but when I tried it it didn't work. My second table occurrence looks just like the original. Do I need to do something else to "compare the results"? I thought the < on the scores would have that effect. The calculation really returned some odd results as well. Any ideas where I might have went wrong? Maybe I don't know enough yet about how FM works under the hood with these self joins. Also, my age group field is from a different related table based on the child's ID number. How do I work that into my results as I can't specify the relationship include fields from another table. Will I need to have an age group field in my results table?

[comment]

It's hard for me to say anything specific about your solution, because all I know about it is that it has "a number of tables that are all working great". My suggestion is based on a typical structure for contests. There would be (at least) a table of Players (Children?), a table of Contests (specific races, meets etc.) and a table of Entries. The Entries table is joining Players and Contests: it has a record for each instance of a player participating in a contest, and when each such participation produces a single result, it is also the scores table.

About age: perhaps contrary to intuition, age is NOT an attribute of a player. The age of a player changes with time. Where age is significant, it is the age of a player at the time of entering a specific contest that we need to consider. This age will not change any further.

Therefore, the Players table needs only a DateOfBirth field, and the actual age (or age group) is calculated once and auto-entered when creating a new record in the Entries tables.