Alias question

[Victoria]

hi - I'm wondering why the following SQL doesn't work. It treats the
'FullName' alias in the last line as a parameter and asks the user for its
value.

SELECT tblEmp.EmpID, tblEmp.LName & ", " & tblEmp.FName AS FullName
FROM tblEmp
ORDER BY tblEmp.FullName

The SQL works fine when I replace the last line by...
ORDER BY tblEmp.LName & ", " & tblEmp.FName ,
but the SQL Help says that ORDER BY can be followed by the name of any
field, and the alias following the 'AS' should have assigned 'FullName' as
the field's new name.

just curious
Victoria

 

[Jeff Boyce]

Victoria

Have you tried using

ORDER BY LName, FName

?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

hi - I'm wondering why the following SQL doesn't work. It treats the
'FullName' alias in the last line as a parameter and asks the user for its
value.

SELECT tblEmp.EmpID, tblEmp.LName & ", " & tblEmp.FName AS FullName
FROM tblEmp
ORDER BY tblEmp.FullName

The SQL works fine when I replace the last line by...
ORDER BY tblEmp.LName & ", " & tblEmp.FName ,
but the SQL Help says that ORDER BY can be followed by the name of any
field, and the alias following the 'AS' should have assigned 'FullName' as
the field's new name.

just curious
Victoria

You're explicitly specifying that you want the FullName field *in table
tblEmp* - and there is no such field. You could just remove the tblEmp from
the ORDER BY clause, but Jeff's advice will give you the correct ordering, and
will use any indexes on tblEmp to make the sort faster; using the calculated
field will defeat the index.

 

[Victoria]

hi Jeff

Yes that works, but my question was why I can't use the alias name in the
ORDER BY line. By the way, the same situation applies in other queries to
the GROUP BY clause.

Victoria

 

[John Spencer]

The problem is that as far as the ORDER BY clause is concerned the FullName
field does not YET exist.

My very rough understanding of the processing of a query is as follows for a
simple SELECT query.

(Please note that I have taken the liberty of using "executes" to mean that an
action of some type is occurring.)

FROM clause "executes" - doing any joins first
WHERE clause "executes" - filtering the records
ORDER BY clause "executes" - ordering the records (note that the Calculated
column is not yet created at this point)
SELECT clause "executes" - to decide which fields (and calculated fields) are
to be returned.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Lynn Trapp]

Victoria,
As John indicated, the Order By clause is actually running before the Select
clause and, thus, it doesn't know what the FullName is yet. However, you can
do it like this and sort on the FullName field:

SELECT EmpID, FullName
FROM
(SELECT tblEmp.EmpID, tblEmp.LName & ", " & tblEmp.FName AS FullName
FROM tblEmp)
ORDER BY FullName;