max of three fields

[weeboll]

Help!

how can I select the max of three fields? I have a table with field1, field2 and field3. I'd like to know which is the greatest value but the max function only works vertically..

any help to save me having to write a monster iif statement or coding in vb would be appriciated!

thanks

WB

 

[ColinEssex]

What are the 3 fields pertaining to? it maybe that your table construction should be modified

Col

 

[weeboll]

What are the 3 fields pertaining to? it maybe that your table construction should be modified

Col

they are condition categories

the table structure (simplified) is id, field1, field2, field3 and I need to know which field reflects the worst condition and ideally update the table with another column (worst condition)

so unfortunatly a restructure is not possible!

 

[ColinEssex]

Silly question - why have you not got a field called "condition"

ID. . . . . . Condition
21. . . . . . Good
68. . . . . . Bad
27. . . . . . Very Good
etc etc

Col

 

[___]

SELECT IIf([Field1]>[Field2],IIf([Field1]>[Field3],[Field1],IIf([Field2]>[Field3],[Field2],[Field3])),IIf([Field2]>[Field3],[Field2],[Field3])) AS HighNumbers, *
FROM YourTable;

 

[weeboll]

SELECT IIf([Field1]>[Field2],IIf([Field1]>[Field3],[Field1],IIf([Field2]>[Field3],[Field2],[Field3])),IIf([Field2]>[Field3],[Field2],[Field3])) AS HighNumbers, *
FROM YourTable;

Thanks but confession time; theres ten fields not 3 I simplified the problem for posting and the size of the iif statement becomes unmanageable!

i've resorted to vb using a simple loop
counter = 0
do
strsql = "update conditiontable set max_condition = '&counter&' where field1 = '&counter&' or field2= '&counter&' or field3 = '&counter&'"
currentdb.execute strsql
counter = counter +1
loop until counter = 6

Cheers though!

WB

ps does anyone have a complete list of ANSI-89 SQL commands?

 

[Mile-O]

Is it too late for normalisation?

 

[weeboll]

Is it too late for normalisation?

normalisation? explain away!

 

[ColinEssex]

normalisation? explain away!

http://www.access-programmers.co.uk/forums/showthread.php?p=273349#post273349