Sort order for union query

[Guest]

Hi,
Can anyone tell me how to change this query to sort by 'officer Surname'.
I've tried everything I can think of but still no good. What i would like is
'All' at the top of the list with surnames sorted under. If I add 'order by'
I need to have sort field in the first part of the query which gives me
multiple "All". Any suggestions?

SELECT 0, "All","","" FROM tblOfficerName UNION SELECT
tblOfficerName.OfficerIDS, tblOfficerName.OfficerSurname,
tblOfficerName.OfficersInitials, tblOfficerName.OfficerRegdNo
FROM tblOfficerName;

 

[tina]

well, i tried a syntax that i saw in a previous post; it worked in a SELECT
query but i couldn't get it to work in the UNION SELECT query.
as an alternate solution, try this

SELECT 0, "All" As OfficerSurname,"","", 0 As X FROM tblOfficerName UNION
SELECT tblOfficerName.OfficerIDS, tblOfficerName.OfficerSurname,
tblOfficerName.OfficersInitials, tblOfficerName.OfficerRegdNo,1
FROM tblOfficerName
ORDER BY X,OfficerSurname

it isn't very graceful, but it does the trick.

hth

 

[Guest]

Thanks Tina, That does work fine. I'm wondering now how others get around
this problem. I'm using this in a combo to allow selection of 'all' or a
specific name to show details in a report. How do other's include all in the
list? is there an easier way?

 

[John Spencer (MVP)]

I usually use " All" or "(All)" or something similar that will sort to the top
based on the leading space or the parentheses.

 

[tina]

well, John and others are in a better position to answer those questions,
sam; i've never used a default value of "all" (or anything similar) in a
combobox RowSource, so i've no prior experience to pass on.

 

[Guest]

Thanks All for your help. I just thought it would be very common to give a
choice of 'all' or a specific item from the list, and thought someone might
have some really nice code....