Inge --

I doodled a bit on your most recent request. It got fairly complex and still I wasn't sure that it would work.

Don't know how tied in to your data structure you are. I wonder, though, if a change in data model might not suit you better. What you have appears to map easily to a data entry form.

But, from the comparison needed, it appears that the t1..3 and goal1..3 values are really the same thing. I wonder if this might not be normalized to the following data model.


PK (? -- not specified in sample code)
tindex(with a range of 1 to 3)
tvalue (t value corresponding to tindex and PK)
goal

One of your current records would generate 3 records in the new data model.
>t1,t2,t3,goal1,goal2,goal3
>10,4,33,5,6,7

tindex: 1
tvalue: 10
goal: 5

tindex: 2
tvalue: 4
goal: 6

tindex: 3
tvalue: 33
goal: 7

etc.


Then, your desired query would be:

SELECT tvalue, goal from table1 ;
    WHERE tvalue IN (SELECT MAX (tValue) FROM table1)

Normalized data structures make creating SQL statements much easier. You may find it worthwhile to make a conversion if you keep running into things like this. Or, if that's not possible, just creating a normalized temporary table may work well for queries like this.

Jay


>t1,t2,t3,goal1,goal2,goal3
>10,4,33,5,6,7
>55,77,8,2,3,4
>21,14,1,9,10,11
>
>1st record: maximum=33
>2nd record: maximum=77
>3rd record: maximum=21
>I need the maximum of these 3 records (maximum of 33,77 & 21) = 77
>(maximum of t1, t2 or t3 of all the records),
>PLUS the corresponding goal. For this example, it would be goal2=2.
>(t1 corresponds to goal1, t2 corresponds to goal2, etc.)
>
>SELECT MAX(IIF(t1>t2,IIF(t1>t3,t1,t3),IIF(t2>t3,t2,t3))) as xRet from table1
>This gives me the max. of t1,t2, & t3 of all the records (thanks for all your help to come up with this one),
>but how do I get the corresponding goal from the corresponding record??
>
>THANK YOU ALL!