If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
UNION question
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. |
#3
|
|||
|
|||
UNION question
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. |
#4
|
|||
|
|||
UNION question
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] |
Thread Tools | |
Display Modes | |
|
|