How to subtract records from a table based on their existence in another

Question

I have an Access 2010 table Assignment

key Empl    Number
key Year    Number
key Week    Number
key Proj    Number

It assigns employees to zero or more projects for given work weeks, thus, all four columns make up the composite key. I have another table, Import, having identical structure, containing data to be used to update Assignment according to different options.

Option 1 Keep all existing assignments, add completely new imports, and ignore any imports that already exist.

I can manage that using a simple append query:

INSERT INTO Assignment 
SELECT *
FROM Import 

So far, so good.

Option 2 As above, except where there's an employee-year-week match between assignment and import, replace the existing assignment with the import.

One approach to achieve that is to first delete all records with an employee-year-week match, then append the imports (as above). The result should be to replace exact matches as-is, replace partial (employee-year-week) matches with new projects, and add completely new assignments.

I can isolate the matches via this query, called Existing:

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week

How can I delete records from Assignment that are also in Existing?

I've tried variations upon this

DELETE 
FROM Assignment
WHERE IN (Existing)

all resulting in error messages.

By the way, it seems that, if there were such an animal, I could use a MINUS (or DIFFERENCE) query:

Assignment
MINUS
Existing

A minor variation on the approach would be to first delete the records with both an employee-year-week match and a project mismatch, then append the imports, as in Option 1. (Fewer deletions and additions, but same result.)

I can isolate those matches via this query, called Existing_exp:

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week AND a.Proj <> i.Proj

but I'm still left with the task of how to delete records from Assignment that are in Existing_exp!

Answer 1

It seems the answer was hiding in plain sight:

DELETE a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week

or

DELETE a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week AND a.Proj <> i.Proj

depending on which is most appropriate.

I don't know how I failed to see it first time around!