Results 1 to 2 of 2

Thread: Union query

  1. #1
    Join Date
    Mar 2006
    Posts
    42

    Unanswered: Union query

    Will you help me built a union query ? I have 2 queries, qryinput and qryoutput.Out of these query i have a third query called qryDiff substracting these
    queries. However qryDiff does not show all the goods sold, only those goods that are substracted.Therefore i want to build an union query but somehow i cannot do it. Will you help ?

    My first query, qryinput is :
    SELECT [order details].ProductID, Sum([order details].Quantity) AS Sum1, orders.orderid
    FROM (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
    GROUP BY [order details].ProductID, orders.orderid;
    My second query, qryoutput is :

    SELECT [order details].ProductID, orders.orderid, [order details].Quantity AS Sum2
    FROM ((orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN Customers ON orders.customerid = Customers.Customerid) INNER JOIN products ON [order details].ProductID = products.Productid;

    My thirs query, qryDiff is :

    SELECT qryProducts.ProductID, Sum(qryInput.Sum1) AS imported, Sum(qryOutput.Sum2) AS exported
    FROM (qryProducts LEFT JOIN qryInput ON qryProducts.ProductID = qryInput.ProductID) LEFT JOIN qryOutput ON qryProducts.ProductID = qryOutput.ProductID
    GROUP BY qryProducts.ProductID;

    Now i want to convert the query qryDiff into an union query, but i cannot do it.I somehow managed to build a simple query showing all the products:
    SELECT ProductID
    FROM qryInput
    UNION SELECT ProductID
    FROM qryOutput;
    This query shows the productsid indeed, but i cannot add the other items form the qryinput and qryoutput as the sum etc.
    I also i cannot convert my query qryDiff into an uinion query, with fields for the imported and the exported sums and also for the product names.
    Where is my error and now could i achieve my aim? My aim is that in the query qryDiff to show all the products, and not only the products sold.


    I will be very grateful for any comments

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Both queries that you want to UNION have to contain the same number of columns, and they need to be in the same order.

    You're going to end up with, at least in some cases, one row from each query, so you have to SUM them with another query. Therefore, you don't need to sum (at least in this case) in the "inner" queries.

    Code:
    SELECT ORDERID, PRODUCTID, SUM(SUM1) AS IMPORTED, SUM(SUM2) AS EXPORTED FROM (
      SELECT orders.orderid, [order details].ProductID, [order details].Quantity
       AS Sum1, 0 as Sum2
      FROM (orders INNER JOIN [order details] ON orders.orderid = 
        [orderdetails].OrderID) INNER JOIN products ON [order details].ProductID = 
        products.Productid
      union all
      SELECT orders.orderid, [order details].ProductID, 0 as Sum1, 
        [order details].Quantity AS Sum2
      FROM ((orders INNER JOIN [order details] ON orders.orderid = 
        [order details].OrderID) INNER JOIN Customers ON orders.customerid = 
        Customers.Customerid) INNER JOIN products ON [order details].ProductID =
        products.Productid
    ) rs
    GROUP BY ORDERID, PRODUCTID
    Inspiration Through Fermentation

Posting Permissions

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