Does anyone know how to retrieve a maximum value from a row (not .

[Don]

I have a table that has a set of 5 scores for each contestant and am trying
to find the top score out of those 5, since only the top score will be used
in calculating where this contestant would place in relation to the other
contestants within the table. I can easily get the top score from each
column, but using this method would allow each contestant to potentially have
more than one score used in calculating the top scores for all the
contestants.

 

[John Spencer (MVP)]

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