UNION question

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.

Correct.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"terry w" wrote in message
...
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 can certainly do that. See this example he
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/Micr..._22657197.html

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"terry w" wrote:

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.

On Sun, 14 Feb 2010 16:04:01 -0800, terry w
wrote:

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.
--

John W. Vinson [MVP]