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.

possible duplicate of [SQL Updating from an inner join](http://stackoverflow.com/questions/12882212/sql-updating-from-an-inner-join) — Bulat, Aug 18 '15 at 16:49
It`s not because mine has a range where as that one does not. — Eduard Anghelescu, Aug 18 '15 at 16:59

FutbolFan · Answer 1 · 2015-08-18T17:18:40.007

1

Is this what you are looking for?

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

edited Aug 18 '15 at 17:18

answered Aug 18 '15 at 16:49

FutbolFan

13,235
3
23
35

1

I`m getting a [syntax error in date in query expression 'tblInvoices.[Activity Date] BETWEEN #[StartDate]#'] – Eduard Anghelescu Aug 18 '15 at 17:06
Sorry, I was using a date variable `#`, please try it again and see if it works. – FutbolFan Aug 18 '15 at 17:19
Access SQL will not let you join `ON TableA.[Date] BETWEEN [StartDate] AND [EndDate]` Unfortunately, the error message will be confusing: *Between operator without And in query expression 'TableA.[Date] BETWEEN [StartDate]'* – HansUp Aug 18 '15 at 17:21
@HansUp Unfortunately I don't have a `MS-Access` readily available to test the code, but I think you are right. Here is where I based my query off of: http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html – FutbolFan Aug 18 '15 at 18:11

HansUp · Accepted Answer · 2015-08-18T16:59:08.020

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];

edited Aug 18 '15 at 16:59

answered Aug 18 '15 at 16:52

HansUp

95,961
11
77
135

When I put this in it requests a number of variables. Did I not implement it right? – Eduard Anghelescu Aug 18 '15 at 18:57
Oh never mind I was being stupid! this works so well! Thanks so much! – Eduard Anghelescu Aug 18 '15 at 20:36

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

2 Answers2