Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Oct 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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;

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    8,831
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

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

    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?
    Please make it as easy as possible for people to help you. Have a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel. For a better answer, put more work into asking a better question.

  3. #3
    New Member
    Join Date
    Oct 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #4
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    8,831
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

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

    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 by Fazza; Nov 8th, 2018 at 10:06 PM.
    Please make it as easy as possible for people to help you. Have a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel. For a better answer, put more work into asking a better question.

  5. #5
    New Member
    Join Date
    Oct 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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;

  6. #6
    Board Regular apr pillai's Avatar
    Join Date
    Aug 2008
    Location
    Kerala, India
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I think, saving both as separate queries (Query1 and Query2) and combine them together in UNION query will work too.
    https://msaccesstips.com(Learn MS-Access Tips and Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •