[AccessD] Question on order within a union query

Thomas,

When I do a union query, I often add in a column for SortOrder to force
the original order:

Select 1 as SortOrder, MyField, MyOtherField from MyTable1
Union
Select 2 as SortOrder, MyField, MyOtherField from MyTable2
Union
Select 3 as SortOrder, MyField, MyOtherField from MyTable3
Order by SortOrder

Then of course you can order by other fields as necessary.


HTH,
 
 
Liz 
 
 
Liz Doering 
elizabeth.j.doering at wellsfargo.com 
612.667.2447 
 
This message may contain confidential and/or privileged information. If
you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose, or take any action based on
this message or any information herein. If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
ewaldt at gdls.com
Sent: Wednesday, August 08, 2007 8:45 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Question on order within a union query

I'm converting an Excel workbook to Access, to add functionality and
better ability for several people to use it at the same time. I've
created the tables, relationships, etc., with no problem. However, I
would like suggestions in one area.

The user likes a spreadsheet report in the original workbook. I'd like
to imitate it for him within Access. This spreadsheet includes several
lines of data (easily duplicated within Access via query), the columns
subtotals, two more corresponding rows of data (i.e., the columns
correspond, but the rows are different), and a final totals line.

I've put together all of the information via queries. I then combine the
queries via a union query, but it insists on mixing the lines together,
alphabetically, by the first column/field. I'm looking for a good way to
avoid this. Is there a key word, command, etc., to tell Access to leave
things in the order they're found?

If not, do you have recommendations on how to achieve what I want in a
different way?

TIA.

Thomas F. Ewald
Stryker Mass Properties
General Dynamics Land Systems






This is an e-mail from General Dynamics Land Systems. It is for the
intended recipient only and may contain confidential and privileged
information.  No one else may read, print, store, copy, forward or act
in reliance on it or its attachments.  If you are not the intended
recipient, please return this message to the sender and delete the
message and any attachments from your computer. Your cooperation is
appreciated.

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com