CODEWITHSUNDEEP
sqlms-accessunion
gdekoker
gdekoker

Reputation: 195

How to do a union between 2 difference tables

I have the below UNION ALL query in Access. The tables are all linked ODBC tables and I am looking to generate a table showing:

Stock Code
Quantity on Hand
Quantity on Order


SELECT dbo_ICITEM.FMTITEMNO, SumOfQUANTITY, SumOfQTYORDERED    
FROM (
    SELECT dbo_ICITEM.FMTITEMNO, Sum(dbo_ICIVAL.QUANTITY) AS SumOfQUANTITY
    FROM dbo_ICIVAL LEFT JOIN dbo_ICITEM ON dbo_ICIVAL.ITEMNO = dbo_ICITEM.ITEMNO
    WHERE (dbo_ICIVAL.LOCATION='1002')
    GROUP BY dbo_ICITEM.FMTITEMNO
    HAVING Sum(dbo_ICIVAL.QUANTITY)<>0

    UNION ALL

    (SELECT dbo_OEORDD.ITEM, Sum(dbo_OEORDD.QTYORDERED) AS SumOfQTYORDERED
    FROM dbo_OEORDD
    WHERE (((dbo_OEORDD.LOCATION) In ('1002')) AND ((dbo_OEORDD.COMPLETE)=0))
    GROUP BY dbo_OEORDD.ITEM
    HAVING Sum(dbo_OEORDD.QTYORDERED) <> 0)
)
;

The stock code and stock on hand seems to populate correctly. I am having an issue with the quantity on order. Access keeps prompting for the "SumOfQTYORDERED" paramater when running the query.

Any idea where I am going wrong?

Upvotes: 0

Views: 71

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you need to fix the union all and add an outer group by:

SELECT FMTITEMNO,
       SUM(SumOfQUANTITY),
       SUM(SumOfQTYORDERED)
FROM ((SELECT i.FMTITEMNO, Sum(v.QUANTITY) AS SumOfQUANTITY,
              0 as SumOfQTYORDERED
       FROM dbo_ICIVAL as v LEFT JOIN
            dbo_ICITEM as i
            ON v.ITEMNO = i.ITEMNO
       WHERE v.LOCATION = '1002'
       GROUP BY i.FMTITEMNO
       HAVING Sum(v.QUANTITY) <> 0
      ) UNION ALL
      (SELECT o.ITEM, 0, Sum(o.QTYORDERED) AS SumOfQTYORDERED
       FROM dbo_OEORDD as o
       WHERE o.LOCATION) In ('1002') AND
             o.COMPLETE = 0
       GROUP BY o.ITEM
       HAVING Sum(o.QTYORDERED) <> 0
      )
     ) as x
GROUP BY ITEM;

This will return one row per item.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

The first Select of a set operation determines the name of the resulting column. Thus there's neither dbo_ICITEM.FMTITEMNO nor SumOfQTYORDERED column in the outer Select.

You can add a dummy NULL column to get three columns per Select and then aggregate them like

SELECT  FMTITEMNO, Sum(SumOfQUANTITY), Sum(SumOfQTYORDERED)
FROM  
 (
    SELECT  dbo_ICITEM.FMTITEMNO, Sum(dbo_ICIVAL.QUANTITY) AS SumOfQUANTITY,
       -- might need a cast to match the datatype of the 2nd Select
       NULL AS SumOfQTYORDERED
    FROM    dbo_ICIVAL 
    LEFT JOIN dbo_ICITEM -- this might return NULLs for dbo_ICITEM.FMTITEMNO?
                         -- do you need the join to dbo_ICITEM at all?
        ON dbo_ICIVAL.ITEMNO = dbo_ICITEM.ITEMNO
    WHERE   (dbo_ICIVAL.LOCATION='1002')
    GROUP BY dbo_ICITEM.FMTITEMNO
    HAVING  Sum(dbo_ICIVAL.QUANTITY)<>0

    UNION ALL

    SELECT  dbo_OEORDD.ITEM, NULL AS SumOfQUANTITY, Sum(dbo_OEORDD.QTYORDERED) AS SumOfQTYORDERED
    FROM    dbo_OEORDD
    WHERE   (((dbo_OEORDD.LOCATION) IN ('1002')) 
        AND ((dbo_OEORDD.COMPLETE)=0))
    GROUP BY dbo_OEORDD.ITEM
    HAVING  Sum(dbo_OEORDD.QTYORDERED) <> 0

 ) dt
GROUP BY FMTITEMNO 
;

Upvotes: 1

LukStorms
LukStorms

Reputation: 29657

Simple really.
A UNION or UNION ALL expects the same number of columns in each unioned select.
With the same types.

So if you want 2 values in the final result then it needs also 2 for them in each select.
If a unioned query doesn't contain that column, then substitute for it.

Simplified example:

select col1, col2, col3
from
(
  select x as col1, 0 as col2, sum(y) as col3
  from table1
  group by x

  union all

  select a, sum(b), 0
  from table2
  group by a
) q;

Notice that only the column aliases of the first select matter for the outer query.

Upvotes: 1

Himanshu
Himanshu

Reputation: 3970

Your first query contains 3 columns and the second query to be unioned contains 2 columns. Hence, the union query is wrong. Either reduce the no of columns or increase likewise as this is basic rule of applying union/union all that the no of columns with its types should be same of the queries to be unioned

Upvotes: 0

Related Questions