Old theme, bad table design. You should have a table for contestants and
another table for contestantScores and then link the two.
If you are stuck with the current design, you can either code a vba function to
get the max value, use a series of nested IIF statements, OR use a normalizing
query to get your data in order.
Normalizing query.
SELECT ContestantID, Score1
FROM YourTable
UNION
SELECT ContestantID, Score2
FROM YourTable
UNION
SELECT ContestantID, Score3
FROM YourTable
UNION
SELECT ContestantID, Score4
FROM YourTable
UNION
SELECT ContestantID, Score5
FROM YourTable
Save that as qFixScores
Now, to get the max score for each contestant.
SELECT ContestantId, Max(Score1) as BigScore
FROM qFixScores
Nested IIF gets complex rapidly, For example here is something that may work for
3 scores assuming that NONE of them are null (blank)
IIF(Score3>IIF(Score5>Score4,Score5,Score4),Score3,IIF(Score5>Score4,Score5,Score4))
A function that does this would be called from your query something like
Field: fGetMaxNumber(Score1,Score2,Score3,Score4,Score5)
Paste the code below into a vba module. It is not the most efficient, but it
should work for you.
'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0) returns 7
'Ignores values that cannot be treated as numbers.
Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double
vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next
If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If
End Function
