How to return the max value from expression-list?

[Guest]

In an Access 2003 SQL Select-query, I want to put the max of a list of
numeric fields from each table row into a new variable, eg:
SELECT ...
Max(Field1, Feld2, Field3, Field4) as GreatestVal
FROM ...

I seem to recall that this non-aggregate Max function worked in earlier
versions of Access, but now errs as having the wrong number of arguments.

 

[Rick Brandt]

In an Access 2003 SQL Select-query, I want to put the max of a list of
numeric fields from each table row into a new variable, eg:
SELECT ...
Max(Field1, Feld2, Field3, Field4) as GreatestVal
FROM ...

I seem to recall that this non-aggregate Max function worked in
earlier versions of Access, but now errs as having the wrong number
of arguments.

Nope. Aggregate queries in a database work against *rows* not a series of
supplied arguments. What you have there looks like an Excel function. You
would need to use a bunch of nested IIf() or Switch() functions or write a
custom function that uses If-Then blocks to determine the highest value.

(the need to do this is caused by an improper table structure in a high
percentage of cases)

 

[Jeff Boyce]

In addition to Rick's solutions, you could consider setting a Reference to
Excel's object model, and using the Excel function in code.

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Thanks Jeff & Rick. The problem is categorizing an ID into the group with
the most months during a year, when any month might have been spent in any
group. My most elegant solution was two nested IIFs, thus:
----------
SELECT A.ID,
Sum(A.SSIdis) as SSID,
Sum(A.Family) as FAMC,
Sum(A.Badger) as BADG,
IIF(SSID>FAMC And SSID>BADG, SSID,
IIF(FAMC>BADG, FAMC, BADG)) as Mval,
IIF(Mval=0,"Other",
IIF(Mval=SSID,"SSIdis",
IIF(Mval=FAMC,"Family","Badger"))) AS Category
FROM A
----------
Not quite as elegant as Oracle's GREATEST(expr1, expr2, ... exprN) function,
but still nice. And in Access SQL you can use a column name you just created
in another calculated column, which you can't do in Oracle to my knowledge.
Writiing the IIFs would get hairier the more fields you have for the
expression-list. If wasn't an earlier version of Access, it might have been
dBase or FoxPro where I used this function before, or Excel.

Jeff, where might I look for an example of your solution? I'm not familiar
with how you set a "Reference to Excel's object model" to make Excel
functions available to Access queries.

Thanks guys.

Paul Rock, Madison, WI

 

[Jeff Boyce]

Paul

Open any code module. Click Tools | References. Select "Microsoft Excel xx
Object ..." (whichever version is loaded on your PC). Save/close the
References window.

Your Access now knows to look at the Excel object for functions. Here's a
sample function that lets you include up to 20 values and returns the
largest:

Public Function fnMaxOfSet(rPar1 As Variant, _
Optional rPar2 As Variant, _
Optional rPar3 As Variant, _
Optional rPar4 As Variant, _
Optional rPar5 As Variant, _
Optional rPar6 As Variant, _
Optional rPar7 As Variant, _
Optional rPar8 As Variant, _
Optional rPar9 As Variant, _
Optional rPar10 As Variant, _
Optional rPar11 As Variant, _
Optional rPar12 As Variant, _
Optional rPar13 As Variant, _
Optional rPar14 As Variant, _
Optional rPar15 As Variant, _
Optional rPar16 As Variant, _
Optional rPar17 As Variant, _
Optional rPar18 As Variant, _
Optional rPar19 As Variant, _
Optional rPar20 As Variant) As Variant

fnMaxOfSet = Excel.WorksheetFunction.Max(rPar1, _
rPar2, _
rPar3, _
rPar4, _
rPar5, _
rPar6, _
rPar7, _
rPar8, _
rPar9, _
rPar10, _
rPar11, _
rPar12, _
rPar13, _
rPar14, _
rPar15, _
rPar16, _
rPar17, _
rPar18, _
rPar19, _
rPar20)

End Function

It's pretty stripped down, mostly a proof-of-concept.

I will suggest/advise that if you have repeating columns (the main reason I
can imagine needing to pick the Max() or Min()), your Access data structure
is probably not well-normalized. This will cause you and Access
considerable headaches, as Access is optimized to work with normalized data
structures.

Good luck!

Jeff Boyce

 

[Michel Walsh]

Hi,


Note that you can use ParamArray to get a variable number of parameters.

---------------------
Public Function Maximum(ParamArray x() As Variant) As Variant
' Return the maximum value among the arguments
Dim t As Variant ' actual value, inspected
Dim running As Variant ' maximum found, at this moment
On Error Resume Next
running = Null
For Each t In x
If IsEmpty(t) Then
ElseIf IsMissing(t) Then
ElseIf IsNull(t) Then
ElseIf IsNull(running) Then
running = t
ElseIf running < t Then
running = t
End If
Next t
Maximum = running
Debug.Assert 0 = Err.Number
Exit Function

End Function
------------------



? Maximum(1, 2, , , 5.6)
5.6


? Maximum("a", "bb", "ax")
bb




Vanderghast, Access MVP