Join two tables with different structure

Question

I have two tables with relationship defined in query, my objective is to combine the two tables without duplicating the inventory and variance information.

The inventory information in table 1 should not duplicate since it is already the summary inventory, while in table two, I need to display it by item since I want to view technician location information.

Also if item from table 1 doesn’t have record in table 2 (vice versa) still I need to display it details. my current workaround for this scenario is to create two queries then export the output in excel then apply some vlookups.

Table 1 Details:

+---------+-----------+----------+-----------+
| ITEMID  | WAREHOUSE | LOCATION | INVENTORY |
+---------+-----------+----------+-----------+
| ITEM001 | WHS001    | LOC001   | 5         |
| ITEM002 | WHS001    | LOC002   | 4         |
| ITEM003 | WHS001    | LOC003   | 2         |
+---------+-----------+----------+-----------+

Table 2 details:

+---------+-----------+----------+--------------+-----------+
| ITEMID  | WAREHOUSE | LOCATION | LOCATIONNAME | INVENTORY |
+---------+-----------+----------+--------------+-----------+
| ITEM001 | WHS001    | T01      | TECH001      | 1         |
| ITEM001 | WHS001    | T02      | TECH002      | 2         |
| ITEM001 | WHS001    | T03      | TECH003      | 3         |
| ITEM002 | WHS001    | T01      | TECH001      | 1         |
| ITEM002 | WHS001    | T02      | TECH002      | 3         |
+---------+-----------+----------+--------------+-----------+

Revised Query:

SELECT
  a.ITEMID, a.WAREHOUSE, a.LOCATION, a.INVENTORY,
  b.ITEMID, b.WAREHOUSE, b.LOCATION, b.LOCATIONNAME, b.INVENTORY,
  (a.INVENTORY-b.INVENTORY) as VARIANCE
FROM Table1  as a
INNER JOIN Table2 as b ON a.ITEMID = b.ITEMID
GROUP by
  a.ITEMID, a.WAREHOUSE, a.LOCATION,
  b.ITEMID, b.WAREHOUSE, b.LOCATION, b.LOCATIONNAME, a.inventory, b.inventory
order by a.ITEMID ASC

UNION ALL

SELECT
  NULL, NULL, NULL, null, 
  t.ITEMID, t.WAREHOUSE, t.LOCATION, t.LOCATIONNAME, t.inventory, NULL
from table2 t
where t.ITEMID > (select MIN (t.itemid) from table2 where  itemid = t.itemid );

Output:

+----------+-------------+------------+-------------+----------+-------------+------------+--------------+-------------+----------+
| a.ITEMID | a.WAREHOUSE | a.LOCATION | a.INVENTORY | b.ITEMID | b.WAREHOUSE | b.LOCATION | LOCATIONNAME | b.INVENTORY | VARIANCE |
+----------+-------------+------------+-------------+----------+-------------+------------+--------------+-------------+----------+
| ITEM001  | WHS001      | LOC001     | 5           | ITEM001  | KITBAG      | T01        | TECH001      | 1           | 4        |
| ITEM001  | WHS001      | LOC001     | 5           | ITEM001  | KITBAG      | T02        | TECH002      | 2           | 3        |
| ITEM001  | WHS001      | LOC001     | 5           | ITEM001  | KITBAG      | T03        | TECH003      | 3           | 2        |
| ITEM002  | WHS001      | LOC002     | 4           | ITEM002  | KITBAG      | T01        | TECH001      | 1           | 3        |
| ITEM002  | WHS001      | LOC002     | 4           | ITEM002  | KITBAG      | T02        | TECH002      | 3           | 1        |
+----------+-------------+------------+-------------+----------+-------------+------------+--------------+-------------+----------+

Desire output:

+----------+-------------+------------+-------------+----------+-------------+------------+--------------+-------------+----------+
| a.ITEMID | a.WAREHOUSE | a.LOCATION | a.INVENTORY | b.ITEMID | b.WAREHOUSE | b.LOCATION | LOCATIONNAME | b.INVENTORY | VARIANCE |
+----------+-------------+------------+-------------+----------+-------------+------------+--------------+-------------+----------+
| ITEM001  | WHS001      | LOC001     | 5           | ITEM001  | WHS001      | T01        | TECH001      | 1           | -1       |
| ITEM001  | WHS001      | LOC001     | 0           | ITEM001  | WHS001      | T02        | TECH002      | 2           | 0        |
| ITEM001  | WHS001      | LOC001     | 0           | ITEM001  | WHS001      | T03        | TECH003      | 3           | 0        |
| ITEM002  | WHS001      | LOC002     | 4           | ITEM002  | WHS001      | T01        | TECH001      | 1           | 0        |
| ITEM002  | WHS001      | LOC002     | 0           | ITEM002  | WHS001      | T02        | TECH002      | 3           | 0        |
| ITEM003  | WHS001      | LOC003     | 2           | NULL     | NULL        | NULL       | NULL         | 0           | 2        |
+----------+-------------+------------+-------------+----------+-------------+------------+--------------+-------------+----------+

Answer 1

Welcome to SO! You should use UNION instead of UNION ALL.

Please, take a look at this question with full description of the difference.

Answer 2

You want to show table1 rows even when they have no match in table2. This is done with an outer join:

select
  t1.itemid,
  t1.warehouse,
  t1.location as t1_location,
  t1.inventory as t1_inventory,
  t2.location as t2_location,
  t2.locationname as t2_locationname,
  (t2.inventory, 0) as t2_inventory,
  t1.inventory - nz(t2.inventory, 0) as variance
from table1 as t1
left join table2 as t2 on t2.itemid = t1.itemid
order by t1.itemid;

I hope MS Access accepts this syntactically. MS Access is just horrible when it comes to SQL standard compliance.

You have the item's warehouse in both tables. Maybe you want to include it in the ON clause, maybe not. You decide :-)