Here is an example query that will select all the engineers who are assigned to a project that overlap with given dates:
Code:
SELECT
Engineers.EngineerID,
Engineers.EngineerName
FROM
Engineers
INNER JOIN
Projects
ON
Engineers.EngineerID = Projects.EngineerID
WHERE
(Projects.ProjectStartDate<=[NEW PROJECT'S END DATE])
AND
(Projects.ProjectEndDate)>=[NEW PROJECT'S START DATE]);
You can also use the list of "unavailable engineers" to select a list of available engineers during that time frame:
Code:
SELECT
Engineers.EngineerID,
Engineers.EngineerName
FROM
Engineers
WHERE
Engineers.EngineerID Not In (
SELECT
UnavailableEngineers.EngineerID
FROM
Engineers AS UnavailableEngineers
INNER JOIN
Projects
ON
UnavailableEngineers.EngineerID = Projects.EngineerID
WHERE
(((Projects.ProjectStartDate)<=[NEW PROJECT'S END DATE])
AND
((Projects.ProjectEndDate)>=[NEW PROJECT'S START DATE]))
);