Sum the best x of y results

[RSGC]

This may be premature, as I am just starting to think about the problem, but I am concerned about how to attack this one. Here is the scenario. I will do my best to make this make sense, I think the problem is fairly simple to describe. I have a table of race results. This results file contains the following fields (among others that are of little importance to this discussion): vessel ID, division ID, race ID, position, and points. Each record represent the specific result of on race. At the end of the season (ad during the season for that matter), we need to produce a report that will list, grouped by division, and sorted with the groups based on the sum of the points field for the BEST x RACES to date. X changes from division to division. So, in some cases we take the best 5 results, in other the best 6, and so forth. Ideally we will DISPLAY ALL results, and total up only the BEST (highest point value) of them for each vessel, and sort the resulting list on the summed result.

Hmm . . . I work with these number a bit so that makes sense to me. I hope it makes sense to someone else. too.

Thanks for any help/guidance you can provide.

Bob

[BobWeaver]

You need to sort by a summary field which you can't do directly. So, you have to do some scripting.

I'm not sure whether you are totalling the points for the best N races by group or by vessel. For this discussion, I will assume that it is by group.

Here are the basic steps:

1. Sort by division [ascending], group [ascending] and points[descending].

2. With a loop, tag the first N records in each group (by setting the value of some field) so that you know that these are the records whose points need to be included in the totals for each group.

3. Calculate the points totals for each of these groups using a calculated field and the Summary function referencing the points field and the tag field that you set in the previous step.

4. Use the replace command to set a number field (call it 'StaticTotal') equal to the value of the points total calculation field. You have to do this because the points total in the calculation field will change when you re-sort the file.

5. Sort the file by Division [ascending] and StaticTotal[descending]

6. Print the report on a layout that has only subsummary parts for division and group (no body part).

I know this is very brief, but it should point you in the right direction. If you have any questions...