Conditional update pulling from another table - Access syntax error

Question

I am a beginner with MS Access.

I have two tables - workrelationship gives dates for every time a person worked for the organization (we apparently terminate and rehire a lot), and workterms gives start/stop dates for every time something changed in the system for an employee - new address, name change, etc. So there can be multiple workterms periods in a workrelationship period.

There's a column POS (period of service id) in workrelationship that I need to copy into workterms for every wt date range that falls within a wr date range. I then need to delete every row in workterms where the dates don't fall into a wr range.

There are ~50k rows in workterms and I need to get this right, so I set up sample tables in Access to play with. Here's a link to the two tables and the error message (see below).

My plan is to pull in the POS first and mark anything that isn't bounded by workrelationship dates "delete". Then I can do a delete on every row that says "delete" in the POS column. (DELETE FROM workterms WHERE pos = 'delete';)

I am getting a syntax error in Access when I do the first part:

UPDATE workterms
SET wt.pos =
    CASE WHEN (wt.termstart >= wr.originalstart 
    and wt.termend <= wr.finalend) 
    THEN wr.pos
    ELSE 'delete' 
    END
FROM workterms wt
INNER JOIN workrelationship wr
ON wr.personid = wt.personid

Any ideas on the syntax error? It's highlighting the word "WHEN" on row 3. I'm not familiar with Access but I have been searching and this looks right compared to what I've found.

EDIT: I tried IIF too:

UPDATE workterms
SET wt.pos =
    IIF ((wt.termstart >= wr.originalstart 
    and wt.termend <= wr.finalend), wr.pos, 'delete')
FROM workterms wt
INNER JOIN workrelationship wr
ON wr.personid = wt.personid;

EDIT 2: HansUp's query below ran just fine (thanks!!), which takes care of the syntax problem.

UPDATE workterms wt
    INNER JOIN workrelationship wr
    ON wr.personid = wt.personid
SET wt.pos =
    IIF(
        wt.termstart >= wr.originalstart AND wt.termend <= wr.finalend,
        wr.pos,
        'delete'
        );

EDIT 3: But - it only brought 3 values in, where I'm expecting 8, as you can see by the select below and the "SELECT from EDIT 3" tab in the linked spreadsheet:

SELECT wr.*, wt.*
FROM workrelationship wr, workterms wt
WHERE wr.personid = wt.personid 
and wt.termstart >= wr.originalstart 
and wt.termend <= wr.finalend

Answer 1

Your current UPDATE query is not giving you the results you desire because the INNER JOIN is only being done on [personid]. So for any [personid] that appears more than once in [workrelationship] your UPDATE query is generating multiple conflicting results.

Consider the following SELECT query that mimics the behaviour of your UPDATE query for [personid]=2:

SELECT 
    IIF(
        wt.termstart >= wr.originalstart AND wt.termend <= wr.finalend,
        wr.pos,
        'delete'
        ) AS result,
    wt.personid,
    wt.termstart,
    wt.termend,
    wt.pos AS wt_pos,
    wr.originalstart,
    wr.finalend,
    wr.pos AS wr_pos
FROM 
    workterms wt
    INNER JOIN 
    workrelationship wr
        ON wr.personid = wt.personid 
WHERE wt.personid = 2
ORDER BY wt.termstart, wr.originalstart

The results are

result  personid  termstart   termend     wt_pos  originalstart  finalend    wr_pos
------  --------  ----------  ----------  ------  -------------  ----------  ------
200-1          2  2010-02-01  2010-03-01  blah    2010-02-01     2010-05-01  200-1 
delete         2  2010-02-01  2010-03-01  blah    2010-07-01     2010-10-01  200-2 
200-1          2  2010-03-01  2010-05-01  blah    2010-02-01     2010-05-01  200-1 
delete         2  2010-03-01  2010-05-01  blah    2010-07-01     2010-10-01  200-2 
delete         2  2010-07-01  2010-10-01  blah    2010-02-01     2010-05-01  200-1 
200-2          2  2010-07-01  2010-10-01  blah    2010-07-01     2010-10-01  200-2 

Notice that each [workterms] row appears twice, once for each corresponding row in [workrelationship], and the [result] is different depending on which [workrelationship] row is joined. So your UPDATE query will actually update each [workterms] row twice. The final value of wt.pos will be whichever value gets applied last, and the order in which the updates are applied is completely up to the query optimizer.

So what you need to do is apply the updates in two passes. Start by setting all wt.pos values to 'delete'

UPDATE workterms SET pos = 'delete'

and then update the wt.pos values for the eight (8) rows you want to keep by using a query that joins on both the [personid] values and the dates:

UPDATE 
    workterms wt
    INNER JOIN 
    workrelationship wr
        ON wr.personid = wt.personid 
            AND wt.termstart >= wr.originalstart 
            AND wt.termend  <= wr.finalend
SET wt.pos = wr.pos

Now your [workterms] table will look like this:

personid  termstart   termend     pos   
--------  ----------  ----------  ------
       1  2010-08-01  2010-09-01  100-1 
       1  2010-03-01  2010-05-01  100-1 
       2  2010-07-01  2010-10-01  200-2 
       2  2010-03-01  2010-05-01  200-1 
       2  2010-02-01  2010-03-01  200-1 
       3  2010-11-01  2015-12-01  delete
       3  2010-09-01  2010-10-01  300-2 
       3  2010-08-01  2010-09-01  300-2 
       3  2010-07-05  2010-07-15  delete
       3  2010-05-01  2010-07-01  300-1