Script to return highest value in separate groups

[solrebel]

I have a DB that is quite simple. It captures name, address, age info. It also captures a lacrosse shot speed. I want to be able to run a script that will return the five highest shot speed results for each unique age group. The age groups can vary between 1-99, there may not be an entry for every age either. What I want to have is: age group X, shot speed (top 5 only).

Are there symbols or operators for defnining the highest values from a field? The shot speeds will vary dramatically so using a range is not really an option.

Any suggestions?

[-Queue-]

What are the age ranges for each group?

[solrebel]

There are no actual groups. That would be part of what I need to separate. I have an age field in the layout. I would need to be able to pick the top five shot speeds for every age represented in the file.

The current method is to export all records as a tab delim text file, open that in Excel and sort by the following columns; sex, age, shot speed. That separates boys and girls and gives me groupings by age and then I can cut all but the top five speeds based on age. I can then save that data and use it in another app. I just don't want to have to go through all of these steps to isolate the data that lives in the FM file.

Maybe that makes it more clear???

[-Queue-]

Yes, I had just thought you meant a range of ages by 'age group'.

Now you've added a detail about separating male and female. Should the top 5 shot speeds be respective of gender (5 males and 5 females for each age)?

[solrebel]

Yes, male and female are separated as well.

Another point to note is that there may not be five or more participants of each age. We may have only one, four year old, no fives or sixes and only two forty-three year olds.

[-Queue-]

See if the script in the attachment works for you.

Lacrosse.zip