I'm struggling to understand the concept.
Surely you have a job role which is either populated with a person or not, and if not then it is vacant
But in principle find all the records in vacancy where there is a related record in saforms with a start date greater that the vacancy start date and less than the vacancy end date - and then exclude those records from vacancy
You can do this with a non standard join (which cannot be displayed in the QBE, has to be SQL. Something like this
Code:
SELECT Vacancy.*
FROM Vacancy V LEFT JOIN SAForms S ON V.VacancyID=S.SAFVacancy AND S.SAFStart>=V.VacancyStart AND S.SAFStart<=V.VacancyEnd
WHERE S.SAFID is Null
You can make a start in the QBE - make the date joins as standard (jeft) joins. Then go to the SQL view and change the two date joins = to >= and <= respectively