Order by in UNION Query (1 Viewer)

I have a union query below, it combines three queries. I want the output to show results in order, so all of first query results, all of second query results and then all of third query results. I have tried several ways found on line using ORDER BY with no luck.

My output always goes back to using the URN as the sort order.

Can someone please explain to me how to accomplish this, Thanks.

SELECT [Master File].AppealID, [Master File].URN, [Master File].ADP_Title, [Master File].ADP_FirstName, [Master File].ADP_MiddleName, [Master File].ADP_LastName, [Master File].Address1, [Master File].Address2, [Master File].Address3, [Master File].suburb, [Master File].State, [Master File].postcode, [Master File].Sol_Code, [Master File].Bequest, [Master File].[Home Phone], [Master File].[Mobile Phone], [Master File].email, "" AS [RTS Code], "" AS [RTS Description], "" AS [Payment Method], "" AS Amount, "" AS [Reject Code], "" AS [Reject Description]
FROM [Master File]
WHERE ((([Master File].[Date Bio Edited])=[Enter Date:]))
UNION SELECT [Master File].AppealID, [Master File].URN, [Master File].ADP_Title, [Master File].ADP_FirstName, [Master File].ADP_MiddleName, [Master File].ADP_LastName, [Master File].Address1, [Master File].Address2, [Master File].Address3, [Master File].suburb, [Master File].State, [Master File].postcode, [Master File].Sol_Code, [Master File].Bequest, [Master File].[Home Phone], [Master File].[Mobile Phone], [Master File].email, [Master File].RTS_Code, [Master File].RTS_Description, "" AS exp1, "" AS exp2, "" AS exp3, "" AS exp4
FROM [Master File]
WHERE ((([Master File].RTS_dte)=[Enter Date:]))
UNION SELECT [Master File].AppealID, [Master File].URN, [Master File].ADP_Title, [Master File].ADP_FirstName, [Master File].ADP_MiddleName, [Master File].ADP_LastName, [Master File].Address1, [Master File].Address2, [Master File].Address3, [Master File].suburb, [Master File].State, [Master File].postcode, [Master File].Sol_Code, [Master File].Bequest, [Master File].[Home Phone], [Master File].[Mobile Phone], [Master File].email, "" AS exp1, "" AS exp2, Transactions.GFPayMeth, Transactions.Amount, Transactions.Reject_Code, Transactions.Reject_Description
FROM [Master File] INNER JOIN Transactions ON ([Master File].AppealID = Transactions.AppealID) AND ([Master File].URN = Transactions.URN)
WHERE ((Not (Transactions.Reject_Code)="08") AND ((Transactions.Rec_Dte)=[Enter Date:]));

Access will try to be helpful by sorting the data is what it thinks is a logical order UNLESS you FORCE it to do otherwise by setting the order using one or more fields

Is there a field in each query that will provide that result, If so, add ORDER BY using it.

If not, then add a dummy number field at the start of each part of the union setting the values as 1,2,3 as follows


EDIT
You really shouldn't have fields with special characters like this

Note that only the field names designated in the first subquery of a Union are meaningful. The fieldnames in the subsequent subqueries are ignored with the values simply appendedin the corresponding order of the fields.

BTW, unless you want the query to test for and remove duplicate records, use UNION ALL.

No need for ALL in that union because of unique ORDER field.

arnelgp said:

No need for ALL in that union because of unique ORDER field.

Click to expand...

Arnelgp:

From this thread and another recently it is clear that you and I have very different perspectives on the use of the ALL keyword in Union queries.

By "no need for ALL" you seem to think ALL is adding something extra to the query. In fact, it is quite the opposite, adding ALL reduces the processing. It is a substantial performance bonus for an extra four key stokes.

Without ALL, the UNION query will check for and supress duplicate records. We already know from the proposed unique Order By that there will be no duplicates. Hence it makes sense to use the ALL keyword to supress a lot of pointless processing and no sense to omit it.

Colin's suggestion of adding another field to establish ordering was good, but he forgot for a moment one of the sensitivies of Access. Instead of using "1 AS ORDER, ..." use some other name to the right of the "AS" because "ORDER" is a keyword and Access does not like field names to match keywords, particularly if the keyword would be part of the same SQL statement.

Oops ....

Try 1 AS MyOrder