Calculation using a formula problem

[Scooter]

I'm a novice at creating formulas. I need to create a field that will calculate and add the largest five values from eight fields in eight separate tables. This value would be stored in a ninth table. Example below:

Table 1

Field: Weight

Value: 10

Table 2

Field: Weight

Value: 8

...

Table 9

Field: Total of the Five Largest Weights from Table 1 thru Table 8

(Need field calcultion formula for this field)

Any help would be appreciated,

Scooter

[MoonShadow]

Join your Total table to each of the others and specify an 'X' for the join. It doesn't matter which two fields you use - any standard field will work. This will relate your Total table to all records in each table. Your weight fields must be number.

Then within your Total table, create a calculation, number with:

Max(Table1::Weight) + Max(Table2::Weight) + Max(Table3::Weight) ... etc.

If you have a lot of records, this calc can slow things down. I would question having 9 different (same/similar) tables, but that is another story.

[MoonShadow]

Hmmm, wait a minute ... you only want the tables added if they contain the top 5 highest values? I think I'd script it or maybe someone else has suggestions. I've seen amazing things with sorting values, particularly Custom Functions. Well, you can create a calculation in your Total Table with something like:

Max(Table 1::Weight) &

[Scooter]

I appreciate the help. It's a simple thing to want but complicated to implement. The application actually involves my Bass Fishing Club. We have 8 Tournaments a year wherein you get to count your best 5, thus I want to have the db tell me at the end of the year the sum of the best 5 results of each anglers' 8 tournaments!

[MoonShadow]

Your tournaments contain the same fields, but are distinguished by data within certain fields, ie, Tournament # and/or Tournament Date etc. This data should, in my opinion, reside in the same table. Why? The same reasons you are bumping up against ... because extracting required data is easier if within one table.

Since noone else has responded, I will give you my (almost worthless) suggestion (short of combining them): Write each angler's total score as records into a summary table (so all exist together) and perform your calculations there.

[Ugo DI LUCA]

Hi,

This would be easier this way for sure, more reliable on the long term if you have 8 tournament a year...

Your total could be based on the sum of the last items of the ValueListItems for scores for each record, which in turn, has pointed by MoonShadow would preferably be scripted because it can slow things depending on the number of records you have.

[Scooter]

Thank you both of you for your time and effort. I'm sure your suggestions will work. Since I'm not a DBA I'm sure this all looks quite amateurish but I volunteered to do this!