title: MS-Access: Calculate Stock From 3 Tables
body: <p>I was reading <a href="https://stackoverflow.com/questions/45202937/summing-totals-from-multiple-tables-in-ms-access">this post</a> from <code>StackOverFlow</code> to adjust query to calculate stock from 3 tables. I have 3 tables <code>tblItems</code>, <code>tblPurchase</code> and <code>tblSales</code>. <code>tblItems</code> contains items information like below.</p>
  <pre><code>ItemCode    Description                 ItemClass   ItemCategory        ReorderLevel    Unit    Stop
  IT000001    Tonner 226A                 Tonner      IT Accessories      10              Pc(s)   No
  IT000002    Keyborad (A4Tech, Slim Key) Keyboard    IT Accessories      10              Pc(s)   No
  IT000003    Mouse (Wireless)            Mouse       IT Accessories      10              Pc(s)   No
  SI000001    A4 Size Paper               Paper       Office Stationary   10              Pc(s)   No
  SI000002    Pen (Black)                 Pen         Office Stationary   10              Pc(s)   No
  SI000003    Pen (Blue)                  Pen         Office Stationary   10              Pc(s)   No
  </code></pre>
  <p>Screenshots of my tables</p>
  <p><em><strong>tblItems:</strong></em></p>
  <p><a href="https://i.stack.imgur.com/oARYo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/oARYo.png" alt="enter image description here" /></a></p>
  <p><em><strong>tblPurchase:</strong></em></p>
  <p><a href="https://i.stack.imgur.com/NJe7b.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NJe7b.png" alt="enter image description here" /></a></p>
  <p><em><strong>tblSales:</strong></em></p>
  <p><a href="https://i.stack.imgur.com/FO1qb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FO1qb.png" alt="enter image description here" /></a></p>
  <p>And my expected result like below</p>
  <p><a href="https://i.stack.imgur.com/pr2Dp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pr2Dp.png" alt="enter image description here" /></a></p>
  <p>In query result items details will come from <code>tblItems</code>. Sum purchase quantity per item and sum sales quantity per item then minus <code>SalesQuantity</code> from <code>PurchaseQuantity</code> to see stock.</p>
  <p>Here is my query which gives me following warning.</p>
  <pre><code>SELECT DISTINCTROW tblItems.ItemCode, TblItems.Description, TblItems.ItemClass, TblItems.ItemCategory, TblPurchaseAgg.[pQuantity], TblSalesAggs.[sQuantity]
  FROM (TblItems 
  
  LEFT JOIN (SELECT TblItems.ItemCode AS ItemCode, Sum(TblPurchase.Quantity) AS pQuantity FROM TblItems LEFT JOIN TblPurchase ON TblItems.ItemCode = TblPurchase.ItemCode GROUP BY TblItems.ItemCode)  
  AS TblPurchaseAgg ON TblItems.ItemCode = TblPurchaseAgg.ItemCode) 
  
  LEFT JOIN 
  
  (SELECT TblItems.ItemCode AS ItemCode, Sum(tblSales.Quantity) AS sQuantity FROM TblItems LEFT JOIN tblSales ON TblItems.ItemCode = tblSales.ItemCode GROUP BY TblItems.ItemCode)  AS TblSalesAggs ON TblItems.ItemCode = TblSalesAggs.ItemCode
  
  GROUP BY TblItems.ItemCode, TblItems.Description, TblItems.ItemClass, TblItems.ItemCategory;
  </code></pre>
  <p><a href="https://i.stack.imgur.com/LhLFT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LhLFT.png" alt="enter image description here" /></a></p>
  <p>Seeking help to resolve the warning and get my expected output. Any help is greatly appreciated. Thanks in advance.</p>