title: Syntax Error in Join Operation in MS-Access when splitting and comparing records
body: <p>Above error message occurs with this statement:</p>
  
  <pre><code> SELECT f.fullname INTO SummaryJudgment_FinalForgottenWithMiddle
  FROM (
  (SELECT Left([aname],InStr(1,[aname],",")-1)) As lastname FROM
  SummaryJudgment_FinalForgotten) &amp; " " &amp; (SELECT
  RIGHT([aname],InStr(1,[aname],",")+1)) As firstname FROM
  SummaryJudgment_FinalForgotten) &amp; " " &amp; (SELECT
  summary_judgment.middle_initial AS middlename FROM summary_judgment)
  ) AS fullname
  FROM SummaryJudgment_FinalForgotten AS f INNER JOIN summary_judgment
  AS s ON f.lastname = s.last_name &amp;&amp; f.firstname = s.first_name;
  </code></pre>
  
  <p>Basically this is what two tables look like (note they will have more fields than 1 where last or first name of different fields can be similar):</p>
  
  <pre><code>SummaryJudgment_FinalForgotten (table)
  aname (field)
  Leventhal,Raymond (data)
  
  
  summary_judgment (table)
  first_name(field)
  Raymond (data)
  last_name (field)
  Leventhal (data)
  middle_initial (field)
  P (data)
  </code></pre>
  
  <p>Ultimately, I'm trying to create a new table that is like
  SummaryJudgment_FinalForgotten but with the middle initial from
  summary_judgment appended:
  Leventhal,Raymond P</p>