MS Access Update a table from another table

I have two tables

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_Sta开发者_如何转开发te
3/21     1       state 3
3/20     2       state 3

Table 1 becomes

Table 1
Date      ID      State
3/22      1       state 1
3/21      2       state 3
3/20      3       state 1

This is what I want to do:

If Table1.ID = Table2.ID and Table1.Date <= 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

How would I do this? I have been trying forever with UPDATE queries.

First thing, "Date" should NEVER be used a a field name, it's a reserved word. You're looking for trouble. I don't even want to write that down, so I will use myDate instead.
Second thing, why use an update query ? 90% of the times they are not necessary. Think twice.

That said, you have to first make a query on table 2 to find out the Min and Max. Something like:

SELECT ID, Min([myDate]) as Start, Max([myDate]) as Finish FROM t2 GROUP BY id

Save that query to qLimits, and make an update query where you join Table1 with qLimits on ID, and set a filter for Table1![myDate] = Between qLimits!Start and qLimits!Finish

That should do it.