Combining Two SELECT TOP Queries into Single Output (UNION Issues)

[Filibuster]

I am trying to combine the output of two SELECT TOP queries into a single query output result. I thought a simple UNION query would do the trick, but ran into problems associated with the ORDER BY statement with the UNION query. The only difference between the two SELECT TOP queries is the parameter of the HAVING criteria. I have tried several workarounds but cannot seem to make the UNION (or any other approach work). Here is the SQL of the two SELECT TOP queries:

SELECT TOP 25 tblTasks.Task, tblTasks.Task_Status, tblTasks.Award_Date, tblTasks.End_Date, Sum(tblPRs.Ceiling_Final) AS SumOfCeiling_Final, tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
FROM (tblTasks INNER JOIN tblClient ON tblTasks.Task_ID = tblClient.Task_ID) INNER JOIN tblPRs ON tblTasks.Task_ID = tblPRs.Task_ID
GROUP BY tblTasks.Task, tblTasks.Task_Status, tblTasks.Award_Date, tblTasks.End_Date, tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
HAVING (((tblTasks.Task_Status)="open"))
ORDER BY Sum(tblPRs.Ceiling_Final) DESC;

SELECT TOP 25 tblTasks.Task, tblTasks.Task_Status, tblTasks.Award_Date, tblTasks.End_Date, Sum(tblPRs.Ceiling_Final) AS SumOfCeiling_Final, tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
FROM (tblTasks INNER JOIN tblClient ON tblTasks.Task_ID = tblClient.Task_ID) INNER JOIN tblPRs ON tblTasks.Task_ID = tblPRs.Task_ID
GROUP BY tblTasks.Task, tblTasks.Task_Status, tblTasks.Award_Date, tblTasks.End_Date, tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
HAVING (((tblTasks.Task_Status)="pending"))
ORDER BY Sum(tblPRs.Ceiling_Final) DESC;

 

[Fazza]

it doesn't seem like a full description of the issue

Just on the side, I suggest using a WHERE clause instead of the HAVING clause.

what happens with a simple UNION ALL

such as,

one query
UNION ALL
other query

What are the problems with that? Can you describe it in words or picture?
Can you describe how whatever result you get differs from the result you want?

 

[Filibuster]

Thanks for the reply Fazza. I did not put a whole lot of additional info in the post (other things I tried, error messages, etc) because I did not want to detract from the essence of the question. Addl info:

1) I did not write the original SELECT TOP SQL. I built the query in Access using Query Design. Query Design added HAVING. I agree maybe not the best approach, but I dont know SQL - so I let Access handle that.
2) UNION is the approach I took to combine the queries - but maybe there is a better one (?).
3) Since Query Design does not allow for UNION, I am attempting to modify the query at the SQL level. Here is the SQL for the UNION

SELECT TOP 25 tblTasks.Task, tblTasks.Task_Status, tblTasks.Award_Date, tblTasks.End_Date, Sum(tblPRs.Ceiling_Final) AS SumOfCeiling_Final, tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
FROM (tblTasks INNER JOIN tblClient ON tblTasks.Task_ID = tblClient.Task_ID) INNER JOIN tblPRs ON tblTasks.Task_ID = tblPRs.Task_ID
GROUP BY tblTasks.Task, tblTasks.Task_Status, tblTasks.Award_Date, tblTasks.End_Date, tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
HAVING (((tblTasks.Task_Status)="open"))


UNION ALL


SELECT TOP 25 tblTasks.Task, tblTasks.Task_Status, tblTasks.Award_Date, tblTasks.End_Date, Sum(tblPRs.Ceiling_Final) AS SumOfCeiling_Final, tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
FROM (tblTasks INNER JOIN tblClient ON tblTasks.Task_ID = tblClient.Task_ID) INNER JOIN tblPRs ON tblTasks.Task_ID = tblPRs.Task_ID
GROUP BY tblTasks.Task, tblTasks.Task_Status, tblTasks.Award_Date, tblTasks.End_Date, tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
HAVING (((tblTasks.Task_Status)="pending"))


ORDER BY Sum(tblPRs.Ceiling_Final) DESC;

When I run it, I get the following error:
"The ORDER BY expression (Sum(tblPRs.Ceiling_Final)) includes fields that are not selected by the query. Only thos fields requested by the first query can be included in an ORDER BY expression."

I tried changing Sum(tblPRs.Ceiling_Final) to the created field name of SumOfCeiling_Final. The query runs but the results are random and do not reflect same results I would get if I ran each query separately.

 

[Fazza]

The ORDER BY can also be : ORDER BY 5 DESC
(to order by the fifth field)

Does that produce what you're after?

PS I didn't test that. After posting I read your error msg properly. You may need a revision something like,

SELECT *
FROM (all the current UNION ALL query you have except the ORDER BY clause)
ORDER BY 5 DESC

That way the ORDER BY is acting only on a single query

 

[Filibuster]

Thanks to those who replied. Here is the solution that worked for me. I generate a derived table from each of my queries, and then UNION the two derived tables.

SELECT *
FROM ( placed first query here
) AS a

UNION ALL

SELECT *
FROM ( place second query here
) AS b;

 

[apr pillai]

I think, saving both as separate queries (Query1 and Query2) and combine them together in UNION query will work too.