Dear all,
Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there.
i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'
AND NOT EXISTS
(
SELECT Person_ID, Department_ID
FROM TBL_PERSON_ALLOCATIONS
WHERE TBL_PERSON_ALLOCATIONS.Department_ID='Form...'
)
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.
Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
Your advice is very much appreciated as I am drawing a blank.
Best
David