Find MAX number within a row

[dvent]

Hi All,

I have a table containing data like follows

NAME NUMBER1 NUMBER2 NUMBER3

JOHN 1 0 0
LUKE 1 2 0
IAN 0 0 3

I would like to write a query that will tell me the max number per Name

i.e.

JOHN 1
LUKE 2
IAN 3

Can anyone assist?

Thanks
dvent

 

[SimonB1978]

Hi,

Here is one solution. Add this code to a module:

Public Function fMax(item1 As Variant, item2 As Variant) As Variant
' Returns the highest of the 2 items
If item1 > item2 Then
fMax = item1
Else
fMax = item2
End If

End Function

Then use it like this in you query:

SELECT Name, fMax(fMax(Number1, Number2), Number3) FROM Table

Be sure to name it something other thant 'MAX' so it is not confused the the built-in SQL function.

HTH

Simon B.

 

[dvent]

Hi,

Here is one solution. Add this code to a module:

Public Function fMax(item1 As Variant, item2 As Variant) As Variant
' Returns the highest of the 2 items
If item1 > item2 Then
fMax = item1
Else
fMax = item2
End If

End Function

Then use it like this in you query:

SELECT Name, fMax(fMax(Number1, Number2), Number3) FROM Table

Be sure to name it something other thant 'MAX' so it is not confused the the built-in SQL function.

HTH

Simon B.

Simon B - that's great!

Can you show me how I would do the same if I had 8 different Numbers i.e. Number1 to Number8? I've tested it for 3 and it works a treat!

 

[Brianwarnock]

A slight modification to Simon's solution, and as we are dealing with numbers I changed the Variants to Longs

Function fmax(num1 As Long, num2 As Long, num3 As Long) As Long
If num1 > num2 Then
fmax = num1
Else
fmax = num2
End If
If fmax < num3 Then
fmax = num3
End If

End Function

use
fmax(Number1,Number2,Number3)

Brian

 

[Brianwarnock]

Simon B - that's great!

Can you show me how I would do the same if I had 8 different Numbers i.e. Number1 to Number8? I've tested it for 3 and it works a treat!

Same principle but extrapolated, but seems to me that you have a design problem, you should not be adding fields or changing code for increased numbers.

Brian

 

[dvent]

Function used:

Function fmax(num1 As Double, num2 As Double, num3 As Double, num4 As Double, num5 As Double, num6 As Double, num7 As Double, num8 As Double) As Double
If num1 > num2 Then
fmax = num1
Else
fmax = num2
End If
If fmax < num3 Then
fmax = num3
End If
If fmax < num4 Then
fmax = num4
End If
If fmax < num5 Then
fmax = num5
End If
If fmax < num6 Then
fmax = num6
End If
If fmax < num7 Then
fmax = num7
End If
If fmax < num8 Then
fmax = num8
End If
End Function

Then in query I used fmax(num1,num2,num3,num4,num5...


I changed the types in the function to double as I have some numbers with decimals.

thanks all!

dvent