Min Across columns

[Tskutnik]

Hi All, A simple question I'm stuck on. Every Answer I can fin has 200 lines of code. I dont need or want code, just the correct syntax for the following.

IN each row in a table, I want to find the Max value across the following three columns [Col1], [Col2], [Col3]

Something like Max([Col1], [Col2], [Col3]) I'd think but I cant get it.

Thanks for the help

 

[Josef P.]

select
   YourID,
   IIF(Col1 > Col2, iif(col1 > col3, col1, col3), iif(col2 > col3, col2, col3)) as MaxValue
from
   YourTable

.. without Null check!
or

select YourID, Max(ColX) as MaxValue
from (
   select YourID, Col1 as ColX from YourTable
   union all
   select YourID, Col2 as ColX from YourTable
   union all
   select YourID, Col3 as ColX from YourTable
) As X
group by
    YourID

Would that be an argument for normalization?

 

[GPGeorge]

"Would that be an argument for normalization? "

That would be a "Yes."

 

[Pat Hartman]

Something like Max([Col1], [Col2], [Col3]) I'd think but I cant get it.

Because you are using a relational database and expecting it to work like a spreadsheet.

I dont need or want code

The solution is to do some reading on normalization so you can create the tables correctly. Then the solution will be a simple query. No code required. However, if you choose to not normalize your schema, then you should prepare yourself to become proficient in VBA because you'll be writing a lot of it.

Just a hint, if you find yourself having to suffix a column name because you have duplicates, you have created a repeating group and instead of having a string of columns, you need a child table with in this case three rows per main form record.

 

[sxschech]

Not to wade into the normalization issue, as there are some cases when reporting may need to adjust data or receiving a download from a third party that needs to be refined in order to import into a normalized schema, here are functions from Allen Browne that may work for your situation.

Function MaxOfList(ParamArray varValues()) As Variant
'http://allenbrowne.com/func-09.html
Dim i As Integer        'Loop controller.
    Dim varMax As Variant   'Largest value found so far.

    varMax = Null           'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
            If varMax >= varValues(i) Then
                'do nothing
            Else
                varMax = varValues(i)
            End If
        End If
    Next

    MaxOfList = varMax
End Function

Function MinOfList(ParamArray varValues()) As Variant
    Dim i As Integer        'Loop controller.
    Dim varMin As Variant   'Smallest value found so far.

    varMin = Null           'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
            If varMin <= varValues(i) Then
                'do nothing
            Else
                varMin = varValues(i)
            End If
        End If
    Next

    MinOfList = varMin
End Function

Example:

SELECT Docs.FileType, Docs.DocumentNo, Docs.DocumentTitle, Docs.DateModified, Docs.RevisionDate, Docs.DateAppended, MaxOfList([datemodified],[revisiondate],[dateappended]) AS MaxDate, MinOfList([datemodified],[revisiondate],[dateappended]) AS MinDate
FROM Docs;

 

[Pat Hartman]

Not to wade into the normalization issue

I guess you missed the part about the OP not wanting to write code

 

[sxschech]

Sorry for adding, only thought in case someone else read the thread looking for ways on how and might be able to find the code option of use.

 

[CJ_London]

You can normalise the view with a union query. Not very efficient but no code