calculate value difference from one # value and the best # value

[accessuserit]

I need help to resolve this problem:

i have this numeric field (value from 1 to 4):

[value1],[value2],[value3],[value4],[value5]

a sample record the value are:

[3],[3],[1],[4],[4]

i need to calculate a value of difference from, for example the field [value1], and the high value in other field.
in this case,the result are -1 (my reference value are 3, and the best are 4)
in other case my value could be 4 and the best of other value 3, in this case, the difference is +1

How is possible to define my primary comparison value?
for example, in one record i need to evaluate a field1, in another, the field3 respect the other. I thinghs to a radio box, but dont know the script for implement this function.

Very, very, very tanks!!!!
Paul

 

[JerryDennison]

Your structure is wrong. Since you're trying to compare values this means each field is related to the other field. This is commonly referred to as "repeating groups". Repeating groups can be within the same field (values seperated by a delimiter such as a comma) or as different fields sharing a commong theme (and usually a common name). Combine these fields into a seperate table and link them back to this table. Then finding your max and min values will be very easy. As it is you'll have to compare each value with every other value to determine the max before you can apply your formula.