CODEWITHSUNDEEP
sqlms-access
gdekoker
gdekoker

Reputation: 195

Combine the result of two select queries into one table

I have the following two tables:

STOCK_ON_HAND: This is showing me all of the stock that I have on hand STOCK_ON_ORDER: This is showing me all of the stock that I have on order

I have the following two queries to summarise the tables:

SELECT STOCK_CODE, SUM(QTY)
FROM STOCK_ON_HAND
GROUP BY STOCK_CODE
HAVING SUM(QTY) <>0;

And

SELECT STOCK_CODE, SUM(ON_ORDER)
FROM STOCK_ON_ORDER
GROUP BY STOCK_CODE
HAVING SUM(STOCK_ON_ORDER) <>0;

I basically want to combine the above with into one table showing the following fields:

What would the best approach to achieve this?

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I would recommend doing a UNION ALL before aggregating:

SELECT STOCK_CODE, SUM(on_hand), SUM(on_order)
FROM (SELECT STOCK_CODE, SUM(QTY) as on_hand, 0 on_order
      FROM STOCK_ON_HAND
      GROUP BY STOCK_CODE
      UNION ALL
      SELECT STOCK_CODE, 0, SUM(ON_ORDER) as on_order
      FROM STOCK_ON_ORDER
      GROUP BY STOCK_CODE
     ) s
GROUP BY STOCK_CODE
HAVING on_hand <> 0 OR on_order <> 0;

If MS Access does not support UNION ALL in the FROM clause, you can use a view to set that up.

Upvotes: 1

forpas
forpas

Reputation: 164099

For this you need a FULL OUTER JOIN which Access does not support directly, but you can simulate it with UNION like this:

SELECT h.STOCK_CODE, h.HQTY AS STOCK_ON_HAND, o.OQTY AS STOCK_ON_ORDER
FROM (
  SELECT STOCK_CODE, SUM(QTY) AS HQTY
  FROM STOCK_ON_HAND
  GROUP BY STOCK_CODE
  HAVING SUM(QTY) <> 0
) h LEFT JOIN (
  SELECT STOCK_CODE, SUM(ON_ORDER) AS OQTY
  FROM STOCK_ON_ORDER
  GROUP BY STOCK_CODE
  HAVING SUM(ON_ORDER) <> 0
) o ON o.STOCK_CODE = h.STOCK_CODE
UNION
SELECT o.STOCK_CODE, h.HQTY AS STOCK_ON_HAND, o.OQTY AS STOCK_ON_ORDER
FROM (
  SELECT STOCK_CODE, SUM(QTY) AS HQTY
  FROM STOCK_ON_HAND
  GROUP BY STOCK_CODE
  HAVING SUM(QTY) <> 0
) h RIGHT JOIN (
  SELECT STOCK_CODE, SUM(ON_ORDER) AS OQTY
  FROM STOCK_ON_ORDER
  GROUP BY STOCK_CODE
  HAVING SUM(ON_ORDER) <> 0
) o ON o.STOCK_CODE = h.STOCK_CODE

If you don't want to see nulls in the results (if they exist) but replace them with 0s, use the function Nz(), like this:

SELECT h.STOCK_CODE, Nz(h.HQTY, 0) AS STOCK_ON_HAND, Nz(o.HQTY, 0) AS STOCK_ON_ORDER


Or get the distinct STOCK_CODEs from both tables and LEFT JOIN them to each of the tables:

SELECT c.STOCK_CODE, h.HQTY AS STOCK_ON_HAND, o.OQTY AS STOCK_ON_ORDER
FROM ((
  SELECT STOCK_CODE FROM STOCK_ON_HAND
  UNION
  SELECT STOCK_CODE FROM STOCK_ON_ORDER
) AS c LEFT JOIN (
  SELECT STOCK_CODE, SUM(QTY) AS HQTY
  FROM STOCK_ON_HAND
  GROUP BY STOCK_CODE
  HAVING SUM(QTY) <> 0
) h ON h.STOCK_CODE = c.STOCK_CODE )
LEFT JOIN (
  SELECT STOCK_CODE, SUM(ON_ORDER) AS OQTY
  FROM STOCK_ON_ORDER
  GROUP BY STOCK_CODE
  HAVING SUM(ON_ORDER) <> 0
) o ON o.STOCK_CODE = c.STOCK_CODE

Upvotes: 1

Related Questions