Sorting numeric fields within a record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to calculate a total based on 4 of 5 scores in a single record.
I need to omit the highest score. I tried using IIF statement within a query
but it doesn't yield the correct result in all cases with test data.

I think it can be done using a module with an array but I can't seem to find
an good example to mimic.

If anyone knows of another why that I am overlooking please let me know, I
could use the help!

Mark
 
Mark:

I have not tested this but you could add a sixth column (F6) with the
following IIF statement:

IIf([F1]>[F2] And [F1]>[F3] And [F1]>[F4] And [F1]>[F5],[F1],IIf([F2]>[F1]
And [F2]>[F3] And [F2]>[F4] And [F2]>[F5],[F2],IIf([F3]>[F1] And [F3]>[F2]
And [F3]>[F4] And [F3]>[F5],[F3],IIf([F4]>[F1] And [F4]>[F2] And [F4]>[F3]
And [F4]>[F5],[F4],[F5]))))

You can then add a seventh column that adds F1-F5 and subtracts F6. This
assumes that none of the five values will be equal to one another. I
believe if you make the comparison ">=" that this should handle cases where
there are ties.


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


message I am trying to calculate a total based on 4 of 5 scores in a single record.
I need to omit the highest score. I tried using IIF statement within a
query
but it doesn't yield the correct result in all cases with test data.

I think it can be done using a module with an array but I can't seem to find
an good example to mimic.

If anyone knows of another why that I am overlooking please let me know, I
could use the help!

Mark
 
Hi Mark,

It sounds as if your record has five "score" fields and you want to
exclude the one with the highest score and sum the others. This sort of
query is fairly simple if the data is normalised - with each score in
its own record - but tends to require custom VBA functions otherwise.
What follows is air code but should give you the idea.


Public Function SumBottom4(S1, S2, S3, S4, S5) As Variant
Dim Max As Long

'If any score is Null we don't know its value and can't
'calculate the total
If IsNull(S1 * S2 * S3 * S4 * S5) Then
SumBottom4 = Null
Exit Function
End If

'Find the maximum value
Max = S1
If S2 > Max Then
Max = S2
End If
If S3 > Max Then
Max = S3
End If
If S4 > Max Then
Max = S4
End If
If S5 > Max Then
Max = S5
End If
'Calculate the result
SumBottom4 = S1 + S2 + S3 + S4 + S5 - Max
End Function





On Wed, 18 May 2005 10:02:12 -0700, Mark H. Trainor <Mark H.
 
Function TotalScore(F1 As Single, F2 As Single, F3 As Single, F4 As Single, _
F5 As Single) As Single
Dim varScores As Variant
Dim intX As Integer
Dim varHighScore As Variant
Dim varTotScore As Variant

varScores = Array(F1, F2, F3, F4, F5)
varHighScore = 0

For intX = 0 To 4
If varScores(intX) > varHighScore Then
varHighScore = varScores(intX)
End If
Next intX

For intX = 0 To 4
varTotScore = varTotScore + varScores(intX)
Next intX

TotalScore = varTotScore - varHighScore
End Function
 
Back
Top