Access Cross tab Query

Question

I have two tables.

I need to subtract the the number of items ordered from the quantity currently recorded.

I can get the count() of the sales of each individual item like so:

SELECT SALES.PRODUCT_ID AS ORDERED_ID
         ,COUNT(SALES.PRODUCT_ID) AS ORDERED
   FROM SALES
   GROUP BY SALES.PRODUCT_ID

Which gives me:

ORDERED_ID  ORDERED
1201    2
1202    2
1204    2
1205    3
1206    1
1207    2
1208    1
1209    1
1210    3

Getting the quantity is just a matter of

SELECT PRODUCT.PRODUCT_ID AS INVEN_ID
         ,PRODUCT.QUANTITY AS INVEN
    FROM PRODUCT

Which gives me:

INVEN_ID    INVEN
1199    5
1200    2
1201    33
1202    44
1203    55
1204    66
1205    77
1206    88
1207    99
1208    110
1209    121
1210    132

I've spent hours on this problem and gave up at what I thought should be the solution:

SELECT SUB1.INVEN - SUB2.ORDERED
FROM 
 (SELECT PRODUCT.PRODUCT_ID AS INVEN_ID
         ,PRODUCT.QUANTITY AS INVEN
    FROM PRODUCT
 )AS SUB1
,(SELECT SALES.PRODUCT_ID AS ORDERED_ID
         ,COUNT(SALES.PRODUCT_ID) AS ORDERED
   FROM SALES
   GROUP BY SALES.PRODUCT_ID
  )AS SUB2
  INNER JOIN SUB1 ON SUB1.INVEN_ID = SUB2.ORDERED_ID

However, access does not recognize that last join as a valid join and without it I just get a Cartesian product. If I try to retrieve quantity without a sub query and just try to SELECT product.quantity - SUB2.ORDERED access demands that I put product.quantity - SUB2.ORDERED in an aggregate function. When I do what it says it then tells me that product.quantity - SUB2.ORDERED can't be in an aggregate function. I'm at a loss.

EDIT:

Final Solution:

SELECT SUB1.INVEN_ID AS PRODUCT_ID
       ,SUB1.PRODUCT_NAME AS PRODUCT_NAME
       ,SUB1.PRICE AS PRICE
       ,SUB1.INVEN - NZ(SUB2.ORDERED,0) AS AVAILABLE
FROM 
 (SELECT PRODUCT.PRODUCT_ID AS INVEN_ID
         ,PRODUCT.PRODUCT_NAME AS PRODUCT_NAME
         ,PRODUCT.PRICE AS PRICE
         ,PRODUCT.QUANTITY AS INVEN
    FROM PRODUCT
 )AS SUB1 
 LEFT  JOIN
(SELECT SALES.PRODUCT_ID AS ORDERED_ID
         ,COUNT(SALES.PRODUCT_ID) AS ORDERED
   FROM SALES
   GROUP BY SALES.PRODUCT_ID
  )AS SUB2 ON SUB1.INVEN_ID = SUB2.ORDERED_ID

D-Shih D-Shih · Accepted Answer · 2018-05-03T07:26:26

Your INNER JOIN should put after first subquery.

I think you are looking for LEFT JOIN,because PRODUCT table should be the master table.

if you use LEFT JOIN SUB2.ORDERED column might be NULL so use NZ function Or IIF(ISNULL(SUB2.ORDERED),0,SUB2.ORDERED) to check.

You can try this.

SELECT SUB1.INVEN - NZ(SUB2.ORDERED,0)
FROM 
(SELECT PRODUCT.PRODUCT_ID AS INVEN_ID
         ,PRODUCT.QUANTITY AS INVEN
    FROM PRODUCT
)AS SUB1
LEFT JOIN
(SELECT SALES.PRODUCT_ID AS ORDERED_ID
         ,COUNT(SALES.PRODUCT_ID) AS ORDERED
   FROM SALES
   GROUP BY SALES.PRODUCT_ID
)AS SUB2 ON SUB1.INVEN_ID = SUB2.ORDERED_ID

Access Cross tab Query

1 Answers