title: MS Access Join sub query
body: <p>I have data in Table2 which I need to use as the key to retrieve information in Table1.  Table2 contains information about the part number including its plant of manufacture and production process.  I need to pass to Table2 the warehouse (plant) and the part number and have it pass back to me, the production plant id and the process id.  I've attempted with the SQL below but its throws a syntax error that simply says "Syntax error in JOIN operation".  I'd rather not have to split into separate sql statements :/</p>
  
  <pre><code>SELECT 
      T1.MFRFMR03 AS Seq, 
      T1.MFRFMR04 AS [Desc], 
      T1.MFRFMR0S AS M2M, 
      T1.MFRFMR0Q AS Std_Labor, 
      T1.MFRFMR0M AS Std_Setup, 
      T1.MFRFMR0R AS Std_Units 
  FROM 
      T1 LEFT JOIN 
                          (
                              SELECT 
                                  T2.MAJRTEPLT, 
                                  T2.MAJRTEID 
                              FROM 
                                  T2 
                              WHERE 
                                  (
                                      ((T2.PLT)=[Enter Plant Number]) 
                                      AND 
                                      ((T2.ITMID)=ucase$([Enter Part Number]))
                                  )
                          ) ON ((T1.MFRFMR01)=(T2.MAJRTEPLT)) AND ((T1.MFRFMR02)=(T2.MAJRTEID))
  WHERE 
      (
          ((T1.MFRFMR0Q)&lt;&gt;0) 
          AND 
          ((T1.MFRFMR0I)&lt;&gt;'S') 
          AND 
          ((T1.MFRFMR0G)=0) 
          OR 
          ((T1.MFRFMR0G)=99999) 
          AND
          (
              ((T1.MFRFMR01)=(T2.MAJRTEPLT)) 
              AND 
              ((T1.MFRFMR02)=(T2.MAJRTEID))
              EXISTS IN 
          )
      )   
  ORDER BY 
      T1.MFRFMR03;
  </code></pre>