Help Sorting a Union Query

[tomh1]

Hello,

I'm trying to create a query for a drop-down list. The selection in the drop-down is used to filter a report or form, and I'd like to have a wildcard value, "(All)", included in the list.

I can do that much; my SQL looks like

SELECT  [Table Employee List].FullName FROM [Table Employee List] UNION SELECT "(All)" FROM [Table Employee List];

However, the result is not sorted in any human-intelligable fashion. What I'd really like to do is take two additional fields, [Table Employee List].LastName and [Table Employee List].FirstName, and sort the list by [LastName], then [FirstName], with "(All)" appearing at the top of the list.

Thank you for your help,

Tom

 

[PHV]

Something like this ?
SELECT FullName, LastName, FirstName
FROM [Table Employee List]
UNION
SELECT "(All)", " ", " "
FROM [Table Employee List]
ORDER BY 2,3;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tomh1]

PHV,

That turns out pretty good, but it still sorts on FullName; not LastName or FirstName.

- Tom

 

[PHV]

Even with the ORDER BY 2,3 clause ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

Try putting the Order by with the first Select:

SELECT FullName, LastName, FirstName
  FROM [Table Employee List] 
ORDER BY 2,3 
UNION
SELECT "(All)", " ", " "
  FROM [Table Employee List]

I am pretty sure that the first select dictates the Sort order and subsequent ORDER BYs are ignored. PHV, do you agree or maybe you can adjust as needed.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

I just tested my suggestion and it may not be correct. Seems to work at the end as PHV has posted.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[tomh1]

Yes, and no.

It wasn't working; now it is.

Maybe I needed to save the query or something to get it to recognize the new sort order.

Thank you for your help!