title: Creating multiple joins
body: <p>So I need to do a product inventory per warehouse. I'm using ms access as front end and SQL server express as a back end. </p>
  
  <p>The query was working fine when I used ms access as linked back end but when I moved my back end to SQL server express when I run the query, a message box is popping up saying:</p>
  
  <blockquote>
    <p>ODBC call failed. [Microsoft][Sql Server Native CLient 11.0][Sql Server] The multipart identifier "Dbo.tbl_Warehouse.warehouse_ID" could not be bound.(#4104)</p>
  </blockquote>
  
  <p>So made a query (<strong>qry_Product_Warehouse</strong>) where each warehouse contains every product</p>
  
  <pre><code>SELECT tbl_Warehouse.Warehouse_ID, tbl_Product.Product_ID
  FROM tbl_Warehouse, tbl_Product;
  </code></pre>
  
  <p>I also have a union query (<strong>Qry_Product_Transactions</strong>) where all the product transactions are, including which warehouse it was transacted. Like <em>[Transaction_Type],[Date],[Product],[Location],[quantity]</em></p>
  
  <p>Then I have a table (<strong>tbl_product_Quantity</strong>) where if the company will do a monthly stock recounting, they will just enter the <em>[Stock_Recount_Date],[Stock_Recount_Quantity],[product],[warehouse]</em>.</p>
  
  <p>The problem is the message box is popping up when I run the query like this (Simplified Version of Joins):</p>
  
  <pre><code>[qry_Product_Warehouse].[warehouse_ID] left join [qry_Product_Transactions].[Location]
  [qry_Product_Warehouse].[warehouse_ID] left join [tbl_Product_Quantity].[warehouse]
  [qry_Product_Warehouse].[product_ID] left join [qry_Product_Transactions].[Product]
  [qry_Product_Warehouse].[Product_ID] left join [tbl_Product_Quantity].[Product]
  </code></pre>
  
  <p>Actual Query is this:</p>
  
  <pre><code>SELECT qry_Product_Warehouse.Warehouse_ID, 
         qry_Product_Warehouse.Product_ID, 
         CDbl(Nz(IIf(IsNull([tbl_Product_Quantity].[Stock_Recount_Date]),
         Sum([qry_Product_Transaction].[qty]),[tbl_Product_Quantity].[Stock_Recount_Quantity]+Sum(IIf([qry_Product_Transaction].[Date]&gt;[tbl_product_quantity].[Stock_Recount_Date],[qry_Product_Transaction].[Qty],0))),0)) AS Current_Qty
  
  FROM (qry_Product_Warehouse LEFT JOIN qry_Product_Transaction ON (qry_Product_Warehouse.Warehouse_ID = qry_Product_Transaction.Location) 
      AND (qry_Product_Warehouse.Product_ID = qry_Product_Transaction.Product)) 
  LEFT JOIN tbl_Product_Quantity ON (qry_Product_Warehouse.Product_ID = tbl_Product_Quantity.Product) 
      AND (qry_Product_Warehouse.Warehouse_ID = tbl_Product_Quantity.Warehouse)
  
  
  GROUP BY qry_Product_Warehouse.Warehouse_ID, qry_Product_Warehouse.Product_ID, tbl_Product_Quantity.Stock_Recount_Quantity, tbl_Product_Quantity.Stock_Recount_Date;
  </code></pre>