Can you sort on a calculated field

[vicodin]

Hi,

I have a query which is something like

select A, B, C, B-C as CalcField1
From Table
Sort by CalcField1

When I run this query it asks for a paramter value for CalcField1 so before I tell my manager to sort it himself manually after running the query is there any simple way to sort on a calculated field?

 

[boblarson]

Yes, you can sort on a calculated field. Can you give the real field names instead of made up ones like you have used?

 

[vicodin]

Fields are
DMACode,
PropID,
Max([All meter reads].ReadDate) - Min([All meter reads].ReadDate) as NumDays,
Max([All meter reads].Reading)-Min([All meter reads].Reading) AS TotConsum,
TotConsum/NumDays AS DailyHHConsump,
DailyHHConsump*365 AS YearlyConsump

Ideally I am looking to sort by yearlyconsump

 

[boblarson]

Okay, you can't use the aliases the way you are attempting if you want to use the field to sort (without saving this query and using it in another query). You would need to use the entire calculation:

((Max([All meter reads].Reading)-Min([All meter reads].Reading))/(Max([All meter reads].ReadDate) - Min([All meter reads].ReadDate)))*365 AS YearlyConsump

 

[vicodin]

Cool, thanks for that.