Hi folks,

How do I create a column in a select query to show the maximum value from any of 4 fields? I tried this way (see below..don't laugh!!) but it doesnt work.......

= Max ( [Goals Scored A] , [Goals Scored B] , [Goals Scored C] , [Goals Scored D] )

Can anyone help please?

Chris.

Try this. It's tedious but should work:

iif(A >= B AND A >= C AND A >= D, A, iif(B >= A AND B >= C AND B >= D, B, iif(C >= A AND C >= B AND C >= D, C, D)))

You do not need to use as many fields ie

IIf(A>B AND A>C AND A>D,A,IIf(B>C AND B>D,B,IIf(C>D,C,D)))

You could use 2 queries as below -
Query1:
SELECT Max(myTable.ColA) AS MaxOfColumn
FROM myTable
UNION ALL
SELECT Max(myTable.ColB)
FROM myTable
UNION ALL
SELECT Max(myTable.ColC)
FROM myTable
UNION ALL
SELECT Max(myTable.ColD)
FROM myTable;

Query 2:
SELECT query1.MaxOfColumn as MaxOfAllColumns
FROM query1;

You guys are great. Thanks a lot.

Chris.