Union Query

[sfoster1]

How do I sort my union select query results?

I have the following union select query:

SELECT Counselor
FROM Counselors
UNION SELECT StaffName
FROM tblOtherStaff
UNION SELECT CaseMgr
FROM tblCaseMgrs
UNION SELECT PsychName
FROM tblPsychs
UNION SELECT SocialWrkr
FROM tblSocWrkrs
UNION SELECT DeanName
FROM tblDeans;

These are all lists in the database that need to be maintained seperately, but which are used for list boxes as the responsible person on certain activities. Here is my problem... I've searched the database and can't figure out how to alphabetize this list for ease of use. To make matters worse, I'd like to sort on the last word in the various field names then the first word in the field names (i.e. last name then first name). They are not broken down into two fields at this point.

 

[FoFa]

A union query always defaults to the columns in the first select, if you want to sort by the one column you have, it would be:
SELECT Counselor
FROM Counselors
UNION SELECT StaffName
FROM tblOtherStaff
UNION SELECT CaseMgr
FROM tblCaseMgrs
UNION SELECT PsychName
FROM tblPsychs
UNION SELECT SocialWrkr
FROM tblSocWrkrs
UNION SELECT DeanName
FROM tblDeans
ORDER BY Counselor;

But if you want to break it apart, I would write a query using your UNION query as the base, break the name apart in that query and sort it there.

 

[Mile-O]

Re: How do I sort my union select query results?

These are all lists in the database that need to be maintained seperately

Why?

Why can't you have one table: tblStaff and another for positions: tblPositions.

tblStaff
StaffID
Forename
Surname
PositionID
etc.

tblPositions
PositionID
Position
etc.


Anyway, as has been said. Base a second query on the results of your UNION query and sort it accordingly.

 

[sfoster1]

Milo- Why? Because I was not thinking clearly! Thanks for the nudge (shove?) in the right direction. I am reverting to your suggestion since it is simpler, easier, more efficient, more effective, etc... You would faint if you saw the whole train wreck of my design. When this project is completely off my back, I am going to study the basics of db design. Thank you again