title: Using INSERT INTO with 'SELECT' to supply some values but not others (Access 2010)
body: <p>I have two tables that look like this:</p>
  
  <pre><code>('MASTER_EOD' TABLE)
  ID  SHA1_HEX    ROLE
  ----------------------------
  1   ff34bb03    2
  2   eef0a005    1
  
  
  
  ('ROLE_INDEX' TABLE)
  ROLE_NUM    ROLE_NAME
  --------------------------
   1           Welcome Calls
   2           Follow Up
   3           Outbound
  </code></pre>
  
  <p>It should be noted that <code>ROLE</code> is joined to <code>ROLE_NUM</code> in the <code>ROLE_INDEX</code> table.</p>
  
  <p>I am trying to write an amend query where I can pass a string from <code>ROLE_NAME</code> (such as 'Follow Up', 'Outbound') etc. and it's associated <code>ROLE_NUM</code> will be put into the '<code>ROLE</code>' column.</p>
  
  <p>I've tried to accomplish this with the following query, as a test;</p>
  
  <pre><code>INSERT INTO master_eod ( sha1_hex, role )
  VALUES ('ef03ff03',(SELECT role_num FROM role_index WHERE role_name='Follow Up'));
  </code></pre>
  
  <p>But I get the following error:
  <strong>Query input must contain at least one table or query.</strong></p>
  
  <p>However, if I put the <code>SELECT role_num FROM role_index WHERE role_name='Follow Up'</code> into it's own query, it works.. Likewise, if I the whole SELECT part with a raw value such as <strong>3</strong>, it works.</p>
  
  <p>What am I missing? I think I might need to use 'INNER JOIN' somehow but I'm still learning the basics of SQL and can't wrap my head around this particular problem and why Access is throwing the error that it does.</p>