I have an Access 2010 table
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
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
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
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
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