Update Query updating data in one if it meets criteria specified in the other

Question

This is all in Access 2007. I have a table with three columns(Pay Period, Start Date, End Date). Another table looks like (Date, Pay period, Other data). I left it as other data because it isn't relevant to the question. I can't figure out how to make an update query to update the pay period in the second table based on which date range it falls under. So if the first table looks like this :

|1| March 17, 2015| April 2, 2015 |
|2| April 3, 2015 | April 17, 2015|

Then all records in the second table that have dates within a date range, should be updated to have the corresponding pay period. So if in the second table there is a record with the date March 27, 2015, the Pay period of the same record should be updated to "1" I've been looking everywhere and haven't found anything.

Answer 1

I stored your sample data in tables named pay_periods and second_table. Then this Access 2010 query updates second_table.[Pay Period] as you requested. It should work the same in Access 2007.

UPDATE second_table AS s, pay_periods AS pp
SET s.[Pay Period] = [pp].[Pay Period]
WHERE s.Date Between [pp].[Start Date] And [pp].[End Date];

Answer 2

Is this what you are looking for?

UPDATE TableA
INNER JOIN TableB ON TableA.[Date] BETWEEN [StartDate]
        AND [EndDate]
SET TableA.PayPeriod = TableB.PayPeriod;