title: MS Access Update a table from another table
body: <p>I have two tables  </p>
  
  <pre><code>Table 1
  Date     ID      State
  3/22     1       state 1
  3/21     2       state 2
  3/20     3       state 1
  
  
  Table 2
  Date     ID      New_State
  3/21     1       state 3
  3/20     2       state 3
  </code></pre>
  
  <p>Table 1 becomes</p>
  
  <pre><code>Table 1
  Date      ID      State
  3/22      1       state 1
  3/21      2       state 3
  3/20      3       state 1
  </code></pre>
  
  <p>This is what I want to do:  </p>
  
  <p>If Table1.ID = Table2.ID and Table1.Date &lt;= Max(Table2.Date) and Table1.Date >= Min(Table2.Date) Then
  I want to change Table1.State to  Table2.New_State where the row used is has the Max Date where Table2.Date >= Table1.Date</p>
  
  <p>How would I do this? I have been trying forever with UPDATE queries.</p>