Union Order By SQL

  • Thread starter Thread starter Bob Quintal
  • Start date Start date
B

Bob Quintal

I'm changing a combo box rowsource with vba and below is my SQL.
It works if I don't use the "ORDER BY" clause, but gives an error
if I include the "ORDER BY" last line. It won't even allow me to
"ORDER BY" shiftID and shiftName. I know it's because of the
Union.

For instance, if I wanted to force the "shiftName" field to sort
in descending order in my example, how could I overcome this
problem while using a Union?


' CODE: ****************

SELECT "0" AS Expr1, "(Both Shifts)" AS Both

FROM myTable AS myTable_1

UNION

SELECT myTable.shiftID, myTable.shiftName

FROM myTable

ORDER BY myTable.shiftName
Click to expand...
Field names in a union query are taken from the first table in the
union. Since you've defined that as Expr1, you need to use that in
the order by clause. Also, you cannot specify the table name, as you
have two tables.
ORDER BY Expr1

Or better yet, rename the fields in the first part.

SELECT "0" AS ShiftID, "(Both Shifts)" AS shiftName
FROM myTable
UNION
SELECT myTable.shiftID, myTable.shiftName
FROM myTable
ORDER BY shiftName
 
I'm changing a combo box rowsource with vba and below is my SQL. It works if
I don't use the "ORDER BY" clause, but gives an error if I include the
"ORDER BY" last line. It won't even allow me to "ORDER BY" shiftID and
shiftName. I know it's because of the Union.

For instance, if I wanted to force the "shiftName" field to sort in
descending order in my example, how could I overcome this problem while
using a Union?


' CODE: ****************

SELECT "0" AS Expr1, "(Both Shifts)" AS Both

FROM myTable AS myTable_1

UNION

SELECT myTable.shiftID, myTable.shiftName

FROM myTable

ORDER BY myTable.shiftName
 
Thank you.

Bob Quintal said:
Field names in a union query are taken from the first table in the
union. Since you've defined that as Expr1, you need to use that in
the order by clause. Also, you cannot specify the table name, as you
have two tables.
ORDER BY Expr1

Or better yet, rename the fields in the first part.

SELECT "0" AS ShiftID, "(Both Shifts)" AS shiftName
FROM myTable
UNION
SELECT myTable.shiftID, myTable.shiftName
FROM myTable
ORDER BY shiftName
Click to expand...
 
Back
Top