Union Query failing on Order By clause

[Corri Atkinson]

Hello All,

I hope it's a simple bug that my untrained eye just isn't catching but
the following Union Query is giving me the error:

"The ORDER BY expression ESTRT_PCMS_ITEM_DATA.REVISION includes fields
that aren't selected by the query. Only those fields selected in the
first query can be included in the ORDER BY expression."

The problem is that this field is right there in the third line of
both queries! I'm pretty lost. Any help would be greatly appreciated.

Sorry it's so long but here's the SQL:
SELECT ESTRTqryBaselineFileName.FILENAME,
ESTRT_PCMS_ITEM_ATTRIBUTES.SCM_ID,
ESTRT_PCMS_ITEM_DATA.REVISION AS REVISION,
ESTRT_PCMS_CHDOC_DATA.CH_DOC_ID,
ESTRT_PCMS_CHDOC_DATA.STATUS,
ESTRT_PCMS_CHDOC_RELATED_ITEMS.RELTYPE_NAME

//Skip over the Joins.. You don't need to read this part most likely

FROM ((ESTRTqryBaselineFileName INNER JOIN (ESTRT_PCMS_ITEM_DATA INNER
JOIN (ESTRT_PCMS_ITEM_ATTRIBUTES INNER JOIN BaselineDifference ON
ESTRT_PCMS_ITEM_ATTRIBUTES.ITEM_UID = BaselineDifference.ITEM_UID) ON
ESTRT_PCMS_ITEM_DATA.ITEM_UID = BaselineDifference.ITEM_UID) ON
ESTRTqryBaselineFileName.ITEM_SPEC_UID =
BaselineDifference.ITEM_SPEC_UID) INNER JOIN
ESTRT_PCMS_CHDOC_RELATED_ITEMS ON BaselineDifference.ITEM_UID =
ESTRT_PCMS_CHDOC_RELATED_ITEMS.TO_ITEM_UID) INNER JOIN
ESTRT_PCMS_CHDOC_DATA ON ESTRT_PCMS_CHDOC_RELATED_ITEMS.FROM_CH_UID =
ESTRT_PCMS_CHDOC_DATA.CH_UID

//Joins Over

WHERE (((ESTRT_PCMS_CHDOC_RELATED_ITEMS.RELTYPE_NAME)<>"Affected"))
ORDER BY ESTRTqryBaselineFileName.FILENAME,
ESTRT_PCMS_ITEM_DATA.REVISION

UNION

SELECT ESTRTqryBaselineFileName.FILENAME,
ESTRT_PCMS_ITEM_ATTRIBUTES.SCM_ID,
ESTRT_PCMS_ITEM_DATA.REVISION AS REVISION,
ESTRT_PCMS_CHDOC_RELATED_ITEMS.FROM_CH_UID AS CH_DOC_ID,
ESTRT_PCMS_CHDOC_RELATED_ITEMS.RELTYPE_NAME, "" AS STATUS

//Joins

FROM ((ESTRT_PCMS_ITEM_DATA INNER JOIN (ESTRTqryBaselineFileName INNER
JOIN BaselineDifference ON ESTRTqryBaselineFileName.ITEM_SPEC_UID =
BaselineDifference.ITEM_SPEC_UID) ON ESTRT_PCMS_ITEM_DATA.ITEM_UID =
BaselineDifference.ITEM_UID) INNER JOIN ESTRT_PCMS_ITEM_ATTRIBUTES ON
BaselineDifference.ITEM_UID = ESTRT_PCMS_ITEM_ATTRIBUTES.ITEM_UID)
LEFT JOIN ESTRT_PCMS_CHDOC_RELATED_ITEMS ON
BaselineDifference.ITEM_UID =
ESTRT_PCMS_CHDOC_RELATED_ITEMS.TO_ITEM_UID

//Joins Over

WHERE (((ESTRT_PCMS_CHDOC_RELATED_ITEMS.FROM_CH_UID) Is Null) AND
((ESTRT_PCMS_CHDOC_RELATED_ITEMS.RELTYPE_NAME) Is Null))

ORDER BY ESTRTqryBaselineFileName.FILENAME,
ESTRT_PCMS_ITEM_DATA.REVISION;

 

[Roger Carlson]

Well, the first thing I see is that you've aliased the field to REVISION.
Try putting that in the ORDER BY instead.

 

[John Viescas]

Change your Order By to:

ORDER BY FILENAME, REVISION

Or

ORDER BY 1, 3

You cannot qualify fields in the Order By clause in a UNION query.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)