UNION question

[terry w]

lets say there is a union query like this;

SELECT tblP.* FROM tblP WHERE ...etc.
UNION
SELECT tblQ.* FROM tblQ WHERE ...etc.
UNION
SELECT tblR.* ... etc.

Now, I want to sort the resulting table by the field common field [PO-type].
Is it proper to use ORDER BY [PO-type] ,without actually mentioning a
table name. I don't know how to use something like ORDER BY
tbl???.[PO-type].

Terry W.

 

[Allen Browne]

Correct.

The ORDER BY clause goes after the last SELECT only, and the field names are
unique in a UNION. (Alias if necessary.)

 

[ryguy7272]

You can certainly do that. See this example here:
http://www.msaccesstips.com/2008/02/union-query.shtml

If you have the same Field names, then you MUST enter the Table name, so
Access knows how ot point to the appropriate records.


Here's another example
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22657197.html

 

[John W. Vinson]

lets say there is a union query like this;

SELECT tblP.* FROM tblP WHERE ...etc.
UNION
SELECT tblQ.* FROM tblQ WHERE ...etc.
UNION
SELECT tblR.* ... etc.

Now, I want to sort the resulting table by the field common field [PO-type].
Is it proper to use ORDER BY [PO-type] ,without actually mentioning a
table name. I don't know how to use something like ORDER BY
tbl???.[PO-type].

Terry W.

You only get one sort... and it needs to be in the *last* SELECT statement.
I'd really avoid using SELECT * in UNION queries; you may be able to get away
with it, but I'd worry that the different tables might have different number
or different order of fields.

Try putting

ORDER BY [PO-type];

at the end of the last SELECT in the UNION; if the fieldname varies from table
to table, you might need to use

ORDER BY 4;

if the PO-Type is the fourth field in the query.