Maximum value in a row

[randommetalguy]

Hey guys,

I have a query where I do a bunch of calculated values. For my next query I need to determine the higher of the two values in a row and then perform calculations on them.

I know how to do this in VB but I'm trying to let the SQL handle all of my calculations.

So. in my query called PulleyDiameterQ I have two fields

PulleyTension: FormatNumber(2*[InputWorkingTension]*1000/(2*[NumberOfCords]*[ASBasicQ.CordDiameterTrimmed]),0)

which currently returns 812

and

PulleyFilament: FormatNumber([ASCordQ.FilamentDiameter]*1000,0)

which currently returns 420

I can't use MAX() unless it's a column so is there any way to use PIVOT to turn this row into a column? Using SQL what's the best why to extract the higher of the two values from my PulleyDiameterQ?

 

[ColinEssex]

If you have values in a row, it sounds to me like you have set up the table in a spreadsheet style instead of a database style (with different tables and columns)

If this is the case I would suggest you consider redesigning it in the proper way because it will lead to more problems further down the line.

Col

 

[Mike375]

IIf([a]>,[a],)

 

[randommetalguy]

If you have values in a row, it sounds to me like you have set up the table in a spreadsheet style instead of a database style (with different tables and columns)

If this is the case I would suggest you consider redesigning it in the proper way because it will lead to more problems further down the line.

Col

Well, the don't have to be in the same table, remember these are the results of a calculated query. I could just as easily make them two separate queries, but I still wouldn't know how to find which one is greater using SQL.

I'll try out the if logic.

 

[Brianwarnock]

I'll try out the if logic.

You make it sound like the If is ify but it isn't, its very simple logic.

Brian