title: Why is LEFT JOIN causing a "multi-part identifier can't be bound" error when INNER JOIN does not?
body: <p>I'm getting the error "The multi-part identifier 'MS2.ExtraValueID' cannot be bound" when trying to run the following SQL query in Access 2010.</p>
  
  <pre><code>SELECT [Contact Details].ContactID
  FROM ([Contact Details] LEFT JOIN [Extra Fields - Newsletters &amp; Bulletins] ON [Contact Details].ContactID = [Extra Fields - Newsletters &amp; Bulletins].ContactID) LEFT JOIN [Extra Fields - Number of Employees] ON [Extra Fields - Newsletters &amp; Bulletins].ContactID = [Extra Fields - Number of Employees].ContactID;
  </code></pre>
  
  <p>ExtraValueID appears in the WHERE clause of both of the "Extra Value..." queries, but isn't outputted.</p>
  
  <p>Looking up <a href="https://stackoverflow.com/questions/206558/what-is-a-multi-part-identifier-and-why-cant-it-be-bound">this question</a> from a few years back, it suggests this is probably due to a typo. However, I can't see anything wrong with the above SQL, which would suggest one of the individual queries which make up this bigger query is wrong.</p>
  
  <p>However, each of the queries individually runs fine, and if I only try to join one of the Extra Fields tables to it, it works fine. I thought the ampersands might be causing an issue, but replacing them with "and", or deleting them altogether makes no difference.</p>
  
  <p>If I replace the <code>LEFT JOIN</code>s with <code>INNER JOIN</code>s, it works inasmuch as it returns results, but not the results I want, because I should return all the results from Contact Details whether there are matching records in the two Extra Fields tables or not.</p>
  
  <pre><code>SELECT [Contact Details].ContactID, [Contact Details].[Client Code], [Extra Fields - Newsletters &amp; Bulletins].Newsletters, [Extra Fields - Number of Employees].[Number of Employees]
  FROM ([Contact Details] INNER JOIN [Extra Fields - Newsletters &amp; Bulletins] ON [Contact Details].ContactID = [Extra Fields - Newsletters &amp; Bulletins].ContactID) INNER JOIN [Extra Fields - Number of Employees] ON [Extra Fields - Newsletters &amp; Bulletins].ContactID = [Extra Fields - Number of Employees].ContactID;
  </code></pre>