Simple inventory SQL query not listing all products MS-ACCESS

Question

So I have this simple SELECT query: design view of the query

SELECT tblProducts.product_ID, Sum(tblIntakes.intake_QTY)-Sum(tblExits.exit_QTY) AS Stock
FROM (tblProducts INNER JOIN tblExits ON tblProducts.product_ID = tblExits.product_ID) 
  INNER JOIN tblIntakes ON tblProducts.product_ID = tblIntakes.product_ID
GROUP BY tblProducts.product_ID;

It doesn't list all the products. It only list those products that had an intake AND an exit. This isn't useful since I would like to know the stock level of all the products.

This is what I get from the query:

| product_ID | Stock |
| 1          | 4     |

This is what I wan't to get

| product_ID | Stock |
| 1          | 4     |
| 2          | 10    |
| 3          | 0     |

This shouldn't be much complicated but I'm new to access and SQL and it's giving me an headache. Any help would be greatly appreciated

These are the records:

products

| product_ID | product_Name |
| 1          | Pencil       |
| 2          | Book         |
| 3          | Marker       |

intakes

| intake_ID | intake_Date | product_ID  | intake_QTY |
| 1         | 20/07/2017  | 1           | 10         |
| 2         | 20/07/2017  | 2           | 10         |

exits

| exit_ID | exit_Date  | product_ID | exit_QTY |
| 1       | 21/07/2017 | 1          | 6        |

Answer 1

You are looking for a left join and nz()

SELECT tblProducts.product_ID,
       nz(Sum(tblIntakes.intake_QTY), 0) - nz(Sum(tblExits.exit_QTY)) AS Stock
FROM (tblProducts LEFT JOIN
      tblExits
      ON tblProducts.product_ID = tblExits.product_ID
     ) LEFT JOIN
      tblIntakes
      ON tblProducts.product_ID = tblIntakes.product_ID
GROUP BY tblProducts.product_ID;