select greater of 2 values

[Guest]

I need to add an expression to a select query that returns the greatest of
several values - like the MAX(a,b,c) function in Excel.

For example, I need to be able to create the "MaxSales" field by saying
MAX(WestSales, EastSales)

Rep WestSales East Sales MaxSales
Smith 100 50 100
Jones 90 200 200

How do I do this in an SQL statement?
(I can't use an iif statement because I need to select from several fields
and calculated values and iif just won't work)

Any help is appreciated.

 

[Marshall Barton]

I need to add an expression to a select query that returns the greatest of
several values - like the MAX(a,b,c) function in Excel.

For example, I need to be able to create the "MaxSales" field by saying
MAX(WestSales, EastSales)

Rep WestSales East Sales MaxSales
Smith 100 50 100
Jones 90 200 200

How do I do this in an SQL statement?
(I can't use an iif statement because I need to select from several fields
and calculated values and iif just won't work)

There's no built in way to do that. That's because the
normalization rules of relational databases say that you
should not have fields like that. Instead, you should have
more tables with simpler records that you can max a set of
records, not a set of columns.

If you're stuck with your speadsheet style tables, then
here's a function will do what you asked:

Public Function MaxOfList(ParamArray vValues() As Variant)
As Variant
Dim vX As Variant

MaxOfList = vValues(0)
For Each vX In vValues
If vX > MaxOfList Then MaxOfList = vX
Next vX
End Function

 

[Guest]

Thanks, Marshall. Seems spreadhseets and db take very different ways of
thinking/