How do I use ORDER BY with a custom column name?

[mindxing]

I'm using Access 2000 and I'm trying to do something like this:

SELECT Employee, Status as ss
FROM data
ORDER BY ss

This query doesn't run since it appears that Access doesn't recognize "ss" as a valid field in the ORDER BY clause. How can I get this effect?

BTW, it won't do for me to use "ORDER BY Status". This is a very simplified version of the actual query I'm trying to use in my program. In the full problem I have columns in two joined tables with the same name. The part of my code that creates the order by part of the query doesn't have knowledge of which field to use for sorting so I wanted to use a generic name there and specify the correct column using the "as" construct. I've used this trick many times while working with MySql databases. I was hoping that I could do the same thing with Access.

Any help will be appreciated.

_M_

[puppydogbuddy]

[size=3][font=Times New Roman][color=black]ORDER BY can work with an alias column if you use "order by N" (N - selected item number).

So, the code below should work:

[/color]SELECT Employee, Status as ss
FROM data
[color=black]ORDER BY 2 'the second item in your selection list[/color][/font][/size]

[mindxing]

Excellent. Thanks.

_M_