Sort order in Union query

[Tom]

Some advice please on how the following can be achieved:

Am using the following union qry for the recordsource in a combo box:

SELECT MemberEmailQ.email, MemberEmailQ.LastName, MemberEmailQ.FirstName,
MemberEMailQ.Main FROM MemberEMailQ UNION SELECT "All","", "" , "" FROM
MemberEMailQ;

which produces results of:


LastName FirstName Main

All
Brown David
Jones Alan x
Smith David


What we want to achive is:

LastName FirstName Main

All
Jones Alan x
Brown David
Smith David

i.e. All followed by the main contact followed by the othe contacts sorted
by LastName desc


Using Access 2002

TIA

Tom

 

[Ken Snell [MVP]]

Try this:

SELECT MemberEmailQ.email, MemberEmailQ.LastName, MemberEmailQ.FirstName,
MemberEMailQ.Main FROM MemberEMailQ UNION SELECT "All","", "" , "a" FROM
MemberEMailQ ORDER BY MemberEMailQ.Main;

 

[Tom]

Ken

Thanks for response but unfortunately not - what we get is

LastName FirstName Main

Brown David
Smith David
All a
Jones Alan x

Have you any other suggestions

Tom

 

[JohnFol]

does it work if you swap round the union?


SELECT "All","", "" , "a" FROM
MemberEMailQ
Union SELECT MemberEmailQ.email, MemberEmailQ.LastName,
MemberEmailQ.FirstName,
MemberEMailQ.Main FROM MemberEMailQ ORDER BY MemberEMailQ.Main;


If not, don't do the order by in the union. Save the union and create a new
Select query on it and specify the order there

 

[Van T. Dinh]

What happens to the "email" Column in your sample data?

Your SQL String would show "All" in the "email" Column, not the "LastName"
Column as per your sample data.

Try

SELECT MEQ.email, MEQ.LastName, MEQ.FirstName,
MEQ.Main, 2 AS AllFirst
FROM MemberEMailQ AS MEQ

UNION ALL
SELECT "No email", "All", "", "" , 1

ORDER BY AllFirst, Main, LastName, FirstName

Adjust the above for whichever Column you want to place "All" but you should
get the idea how to sort "All" first, then by Main, then by LastName and
finally FirstName.

 

[Ken Snell [MVP]]

Sorry... slight error on my part.

SELECT MemberEmailQ.email, MemberEmailQ.LastName, MemberEmailQ.FirstName,
MemberEMailQ.Main FROM MemberEMailQ UNION SELECT "All","", "" , "z" FROM
MemberEMailQ ORDER BY MemberEMailQ.Main DESC;

 

[Ken Snell [MVP]]

(sent last reply a moment too soon)...

Sorry... slight error on my part.

SELECT MemberEmailQ.email, MemberEmailQ.LastName, MemberEmailQ.FirstName,
MemberEMailQ.Main FROM MemberEMailQ UNION SELECT "All","", "" , "z" FROM
MemberEMailQ ORDER BY MemberEMailQ.Main DESC, MemberEmailQ.LastName;


--

Ken Snell
<MS ACCESS MVP>

 

[Tom]

Hi Van

The client is able to select either individual email address's from that
column - hence the need to have a lastName & firstname column which are then
added to a table - all email address in that table are then sent an email.
If All is selected, all email address within the MemberEMailQ are added to
that table and hence it is a little academic if All refers to All members
email (address) or all members LastName.

Trying to run your code I get the following error message: "Query input must
contain at least one table or query" - any suggestions please

Tom

 

[Ken Snell [MVP]]

< sigh > some days....

Corrected another typo:

SELECT MemberEmailQ.email, MemberEmailQ.LastName, MemberEmailQ.FirstName,
MemberEMailQ.Main FROM MemberEMailQ UNION SELECT "All","", "z" , "" FROM
MemberEMailQ ORDER BY MemberEMailQ.Main DESC, MemberEmailQ.LastName;

 

[Van T. Dinh]

Sorry. Left out the FROM clause in the second select.

Try

SELECT MEQ.email, MEQ.LastName, MEQ.FirstName,
MEQ.Main, 2 AS AllFirst
FROM MemberEMailQ AS MEQ

UNION
SELECT "No email", "All", "", "" , 1
FROM MemberEMailQ AS Dummy

ORDER BY AllFirst, Main, LastName, FirstName

 

[Tom]

Thanks Ken, JohnFol and Van for your input this gives us the solution to
our problem

Tom