Find highest value in each row.

[Marshallp24]

Hi,

I have a problem trying to find the highest value for each row. I have
a query, which adds up scores. Its based on a table something like
this

orgID Score1 Score2 Score3 Score4 Score5 Score6
1 10 1 5 2 11 20
2 1 4 2 12 11 24
1 10 7 15 12 1 2

and so on.....

I have query that basicly adds up some of the scores into groups. So
its like:

orgID ScoreSet1:[Score1]+[Score2]+[Score3] ScoreSet2:[Score4]+[Score5]+
[Score6]
1 16 33
2 7 47
3 32 15


So I want to find the highest of the 2 scoresets. So it'd look like:

orgID ScoreSet1 ScoreSet2 HighestScoreSet
1 16 33 33
2 7 47 47
3 32 15 32

How can I do this?

Tried Max(ScoreSet1, ScoreSet2), but it didnt work.

any ideas?

 

[Stefan Hoffmann]

hi Marshall,

I have a problem trying to find the highest value for each row. I have
a query, which adds up scores. Its based on a table something like
this

orgID Score1 Score2 Score3 Score4 Score5 Score6
1 10 1 5 2 11 20
2 1 4 2 12 11 24
1 10 7 15 12 1 2

and so on.....

You need to normalize your table than you can use Max()/Min() or DMax(),
DMin().


mfG
--> stefan <--

 

[Guest]

HighestScoreSet: IIF([ScoreSet1]>[ScoreSet2], [ScoreSet1], [ScoreSet2])

 

[Ofer Cohen]

Hi,

Create a function within a module that recieve a string of numbers seperated
by "-", and return the highst value

Function GetHighNumber(NumberString As String) As Double
On Error GoTo GetHighNumber_Err
Dim I
GetHighNumber = 0
' you can change the number for the loop, depend on how many numbers you
send
For I = 0 To 20
If GetHighNumber < Val(Nz(Split(NumberString, "-")(I), 0)) Then
GetHighNumber = Val(Nz(Split(NumberString, "-")(I), 0))
End If
Next I
Exit Function
GetHighNumber_Err:
If Err <> 9 Then MsgBox Error

End Function

*********************************************
In your query you can use this function, by creating a field

HighestScoreSet: GetHighNumber([Score1] & "-" & [Score2] & "-" & [Score3] &
"-" & [Score4] & "-" & [Score5] & "-" & [Score6])

 

[John Spencer]

Dale Fye posted this VBA function quite a while back.

You can use it in a query (or elsewhere).

'Copy and paste this into a module and save the module with a name other
than "MaxVal".

Public Function MaxVal(ParamArray MyArray()) As Variant

Dim varMax As Variant
Dim intLoop As Integer
varMax = Null

For intLoop = LBound(MyArray) To UBound(MyArray)
If IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf IsNull(varMax) Or MyArray(intLoop) > varMax Then
varMax = MyArray(intLoop)
End If
Next
MaxVal = varMax

End Function

Now in a query you can call that with

Field: MaxScore: MaxVal(ScoreSet1, ScoreSet2)

In a query you are limited to passing a maximum of 29 arguments to the
function (at least in 2003 and earlier)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I have a problem trying to find the highest value for each row. I have
a query, which adds up scores. Its based on a table something like
this

orgID Score1 Score2 Score3 Score4 Score5 Score6
1 10 1 5 2 11 20
2 1 4 2 12 11 24
1 10 7 15 12 1 2

and so on.....

I have query that basicly adds up some of the scores into groups. So
its like:

orgID ScoreSet1:[Score1]+[Score2]+[Score3] ScoreSet2:[Score4]+[Score5]+
[Score6]
1 16 33
2 7 47
3 32 15


So I want to find the highest of the 2 scoresets. So it'd look like:

orgID ScoreSet1 ScoreSet2 HighestScoreSet
1 16 33 33
2 7 47 47
3 32 15 32

How can I do this?

Tried Max(ScoreSet1, ScoreSet2), but it didnt work.

any ideas?