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

Filibuster

New Member
Joined
Oct 17, 2013
Messages
7
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:

Code:
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;


Code:
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;
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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?
 
Upvote 0
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

Code:
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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.

Code:
SELECT *
FROM ( placed first query here
) AS a

UNION ALL

SELECT *
FROM ( place second query here
) AS b;
 
Upvote 0
I think, saving both as separate queries (Query1 and Query2) and combine them together in UNION query will work too.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top