Return largest number across columns

[Newbeetle]

Hi I have four columns in a query called C1, C2, C3, C4 and I want a further
column in the query to return the highest number.

So if i have values of 4, 6, 2, 1 I'm looking to return 6

Somtimes some of the columns may have no value.

Thanks in advance.

 

[Jeff Boyce]

What you described sounds like the Maxiumum function ... in a spreadsheet!
Is there a reason you aren't using Excel to do this?

(hint: having 'repeating columns' with similar values -- all these appear
to be numbers of somethings -- is an indication that your table structure is
not well-normalized. So what? you ask? Access' features and functions
expect well-normalized data...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MikeB]

Hi I have four columns in a query called C1, C2, C3, C4 and I want a further
column in the query to return the highest number.

So if i have values of 4, 6, 2, 1 I'm looking to return 6

Somtimes some of the columns may have no value.

Thanks in advance.

Not easy, and perhaps wrong in many different ways but you could try
this.

Write a Union Query to "normalize" your data into a different format
where there is a field indicating the "row" of your previous data,
another field for the "Column" value (C1, C2 etc. in your example) and
then finally a field for the actual value in each column.

Now write a Query that gets the Max value for each of the set of "row"
records.

Join this query back to your original table and voila!

 

[Clifford Bass]

Hi Newbeetle,

Assuming that there is a good reason for keeping the table in its
current form or that you have no control over the database design, you can
use this function that I wrote some time ago.

-----------------------------------------------------------------------------

Public Function MaximumAny(ParamArray var() As Variant) As Variant

' Maximum function that accepts any number of parameters.

' Parameters: Any number of items accepted
' Returns: The maximum of all values, if there are any non-null values,
otherwise Null

' Author: Clifford Bass

Dim i As Integer
Dim j As Integer
Dim varMaximum As Variant
Dim varSubMaximum As Variant

On Error GoTo MaximumAny_Error

varMaximum = Null
For i = LBound(var()) To UBound(var()) Step 1
If Not IsNull(var(i)) Then
If IsArray(var(i)) Then
For j = LBound(var(i)) To UBound(var(i)) Step 1
varSubMaximum = MaximumAny(var(i)(j))
If Not IsNull(varSubMaximum) Then
If IsNull(varMaximum) Then
varMaximum = varSubMaximum
Else
If varSubMaximum > varMaximum Then
varMaximum = varSubMaximum
End If
End If
End If
Next j
Else
If IsNull(varMaximum) Then
varMaximum = var(i)
Else
If var(i) > varMaximum Then
varMaximum = var(i)
End If
End If
End If
End If
Next i
MaximumAny = varMaximum

MaximumAny_Exit:
Exit Function

MaximumAny_Error:
MaximumAny = strError
Resume MaximumAny_Exit

End Function

-----------------------------------------------------------------------------

In a query you can specify up to 29 columns. It will give the maximum
of a variety of types of objects (text, numbers, dates). When calling from
code the number of items is supposedly unlimited and can contain arrays of
objects. Your query item would loook something like this:

Max_of_C: MaximumAny([C1], [C2], [C3], [C4])

Hope that helps,

Clifford Bass

 

[John Spencer]

Use a custom vba function.
'------------- Code Starts --------------

Dale Fye's Version
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

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Michael J. Strickland]

Hi I have four columns in a query called C1, C2, C3, C4 and I want a
further
column in the query to return the highest number.

So if i have values of 4, 6, 2, 1 I'm looking to return 6

Somtimes some of the columns may have no value.

Thanks in advance.

Try entering this in a new column of your query:

Expr1: IIf([C1]>=[C2] And [C1]>=[C3] And [C1]>=[C4],[C1],IIf([C2]>=[C1]
And [C2]>=[C3] And [C2]>=[C4],[C2],IIf([C3]>=[C1] And [C3]>=[C2] And
[C3]>=[C4],[C3],IIf([C4]>=[C1] And [C4]>=[C2] And [C4]>=[C3],[C4]))))

This is for 4 columns. It can get quite monstrous when extrapolating to
more
columns so one of the programs already mentioned is probably your best
bet.


--

 

[Jeff Boyce]

Michael

Please note that a Null in any one/more of the columns will "break" the
expression...

You might want to incorporate the Nz() function just in case...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi I have four columns in a query called C1, C2, C3, C4 and I want a
further
column in the query to return the highest number.

So if i have values of 4, 6, 2, 1 I'm looking to return 6

Somtimes some of the columns may have no value.

Thanks in advance.

Try entering this in a new column of your query:

Expr1: IIf([C1]>=[C2] And [C1]>=[C3] And [C1]>=[C4],[C1],IIf([C2]>=[C1]
And [C2]>=[C3] And [C2]>=[C4],[C2],IIf([C3]>=[C1] And [C3]>=[C2] And
[C3]>=[C4],[C3],IIf([C4]>=[C1] And [C4]>=[C2] And [C4]>=[C3],[C4]))))

This is for 4 columns. It can get quite monstrous when extrapolating to
more
columns so one of the programs already mentioned is probably your best
bet.


--

 

[BruceM]

In addition to what Jeff mentioned, I don't think there is any need for the
last IIf. Once nulls are accounted for one of the logical tests will pass,
so if it is not C1, C2, or C3 it can only be C4. No need to test.
Expr1: IIf([C1]>=[C2] And [C1]>=[C3] And [C1]>=[C4],[C1],
IIf([C2]>=[C1] And [C2]>=[C3] And [C2]>=[C4],[C2],
IIf([C3]>=[C1] And [C3]>=[C2] And [C3]>=[C4],[C3],[C4])))

Hi I have four columns in a query called C1, C2, C3, C4 and I want a
further
column in the query to return the highest number.

So if i have values of 4, 6, 2, 1 I'm looking to return 6

Somtimes some of the columns may have no value.

Thanks in advance.

Try entering this in a new column of your query:

Expr1: IIf([C1]>=[C2] And [C1]>=[C3] And [C1]>=[C4],[C1],IIf([C2]>=[C1]
And [C2]>=[C3] And [C2]>=[C4],[C2],IIf([C3]>=[C1] And [C3]>=[C2] And
[C3]>=[C4],[C3],IIf([C4]>=[C1] And [C4]>=[C2] And [C4]>=[C3],[C4]))))

This is for 4 columns. It can get quite monstrous when extrapolating to
more
columns so one of the programs already mentioned is probably your best
bet.


--