Max() statement assistance required 1

[jason321]

I'm trying to make a query in Access but cannot figure out how to do it - here is what works in Sql Server
select rate1, rate2,
(select max(rate)
from (select rate1 as rate
union all
select rate2 as rate
) as a) as MaxRate
from dbo.rates;
Please help - thanks

 

[PHV]

Perhaps this ?

SELECT rate1, rate2,
 (SELECT Max(rate) FROM (
     (SELECT rate1 AS rate FROM tblRates
      UNION ALL SELECT rate2 FROM tblRates
     ) AS A
 ) AS MaxRate
FROM tblRates

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jason321]

Thanks for the reply PHV -
It gives me a column at least but it takes the max of all the rates and puts that value in every field - seems some sort of cartesian product too for some reason. I've tried grouping but that doesn't seem to work either.

 

[PHV]

Sorry, misunderstood your unformulated question ...
SELECT rate1, rate2, IIf(rate1<rate2,rate2,rate1) AS MaxRate FROM tblRates

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jason321]

That's the one! Thanks PHV