title: Creating a new record containing values based on queries of other tables in MS-Access
body: <p>I want to create a new record in the mailers table, where three fields would contain values: contacts_first_filter_id, mailer_states_id, created_at. And two of those values are based off queries from other tables and the last one is just the current time. </p>
  
  <p>I've tried multiple ways to achieve this but none have worked. First I tried creating a query directly within sql design:</p>
  
  <pre><code>INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
  VALUES (DLookup("id","update_mailer_step_two"), DLookup("id","mailer_states" &amp; "mailer_state = 'sent'"), Now());
  </code></pre>
  
  <p>This gives error 'unknown'.</p>
  
  <p>I also tried putting this in VBA editor and triggering it on a button click:</p>
  
  <pre><code>Private Sub Command6_Click()
  
  
          Dim CFF_ID As String, MS_ID As String, strSQL As String
  
          CFF_ID = "SELECT update_mailer_step_two.id FROM update_mailer_step_two"
  
          MS_ID = "SELECT mailer_states.id FROM mailer_states WHERE mailer_states.mailer_state = 'sent'"
  
          strSQL = "INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )VALUES ((" &amp; CFF_ID &amp; "),(" &amp; MS_ID &amp; "),NOW())"
  
          DoCmd.RunSQL strSQL
  
  End Sub
  </code></pre>
  
  <p>This gives error:
  "Query input must contain at least one table or query". </p>
  
  <p>I tried the recommendation below using INNER JOIN but while this produces no error, it appends 0 rows, presumably because there is no INNER JOIN link between update_mailer_step_two and mailer_states:</p>
  
  <pre><code>INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
  SELECT update_mailer_step_two.id, mailer_states.id, Now()
  FROM update_mailer_step_two INNER JOIN mailer_states ON update_mailer_step_two.id = mailer_states.ID
  WHERE mailer_states.mailer_state = 'sent';
  </code></pre>
  
  <p>mailer_states table is just a table that contains a mailer_state field with text. It's pretty much just a lookup table that never changes.</p>
  
  <p>update_mailer_step_2 contains one field called id, which contains a bunch of ids associated with contacts in database.
  Hence, there's no relationship at all between mailer_states and update_mailer_step_2.</p>
  
  <p>** What I thought I would be able to do is when I create update_mailer_step_two, add a new column and default it with a value corresponding to mailer_states so that the INNER JOIN would work. Unfortunately, it appears you cannot do this in Access!</p>
  
  <p>Another stackoverflow post mentioned you can:</p>
  
  <p><a href="https://stackoverflow.com/questions/47535/sql-to-add-column-with-default-value-access-2003">SQL to add column with default value - Access 2003</a></p>
  
  <p>But it is not working for me.</p>
  
  <p>I have run out of ideas of how to place a query based off other table as a value to a query that is intended to create a new record in a table.</p>
  
  <p>Thanks for response. </p>