Error while running Union Query

Question

Good Morning,

I am trying to run a union query using the following SQL:

SELECT [OWL Tbl].[User Name], [OWL Tbl].Category, [OWL Tbl].[End Status], CDate(Int([Report Date/Time])) AS [Report Date], [OWL Tbl].MonthEnd

FROM [OWL Tbl]

WHERE ((([OWL Tbl].[End Status]) Not Like "Closed - Needs Validation" And ([OWL Tbl].[End Status]) Not Like "Closed - Validated" And ([OWL Tbl].[End Status]) Not Like "Closed - Validation Rejected" And ([OWL Tbl].[End Status]) Not Like "Closed - Validation Review"))

ORDER BY [OWL Tbl].[User Name], [OWL Tbl].[End Status], CDate(Int([Report Date/Time]));

union

SELECT [OWL Tbl].[User Name], "Validation" AS Category, [OWL Tbl].[End Status], CDate(Int([Report Date/Time])) AS [Report Date], [OWL Tbl].MonthEnd

FROM [OWL Tbl]

WHERE ((([OWL Tbl].[End Status]) Like "Closed - Needs Validation" Or ([OWL Tbl].[End Status]) Like "Closed - Validated" Or ([OWL Tbl].[End Status]) Like "Closed - Validation Rejected" Or ([OWL Tbl].[End Status]) Like "Closed - Validation Review"))

ORDER BY [OWL Tbl].[User Name], [OWL Tbl].[End Status], CDate(Int([Report Date/Time]));

but I keep getting the following Error message:

The ORDER BY expression (CDate(Int([Report Date/Time]))) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.

Can anybody tell me what I am doing wrong?

Any help is apprecaited!!!!

Answer 1

Order by can only appear at the very end of the statement, not for each clause.

Answer 2

I took the Order By clause out completely. I was able to get the query to run after that, however it was grouping on the [End Status] field when I didn't want it to. I was able to fix this by adding in a unique reference number field. I am now getting what I need. Thank you for your help! I appreciate it!

Answer 3

I took the Order By clause out completely. I was able to get the query to run after that, however it was grouping on the [End Status] field when I didn't want it to. I was able to fix this by adding in a unique reference number field. I am now getting what I need. Thank you for your help! I appreciate it!

Answer 4

I'd suggest a couple of changes. As Tom says, the ORDER BY must appear in the last SELECT statement, but it must refer to the fieldnames in the first.

You're also using NOT LIKE inappropriately. The LIKE operator is for use with wildcards (# for a digit, ? for any character, * for any string); if there are no wildcards it's identical in effect to the = operator. Since you're excluding several strings, try using NOT IN() instead:

SELECT [OWL Tbl].[User Name], [OWL Tbl].Category, [OWL Tbl].[End Status], CDate(Int([Report Date/Time])) AS [Report Date], [OWL Tbl].MonthEnd

FROM [OWL Tbl]

WHERE ((([OWL Tbl].[End Status]) Not In ("Closed - Needs Validation","Closed - Validated","Closed - Validation Rejected", "Closed - Validation Review")))

union

SELECT [OWL Tbl].[User Name], "Validation" AS Category, [OWL Tbl].[End Status], CDate(Int([Report Date/Time])) AS [Report Date], [OWL Tbl].MonthEnd

FROM [OWL Tbl]

WHERE ((([OWL Tbl].[End Status]) IN("Closed - Needs Validation","Closed - Validated","Closed - Validation Rejected" ,"Closed - Validation Review")))

ORDER BY [OWL Tbl].[User Name], [OWL Tbl].[End Status], CDate(Int([Report Date/Time]));