Filter Query

[romi]

Dear All..
I Have a table Employee

Employee Name	Start Date	End Date
Smith	12/01/2017	12/03/2017
Jack	12/02/2017	12/05/2017
Smith	12/05/2017	12/08/2017
James	12/07/2017	12/09/2017

I want to assign job Start Date 12/04/2017 and End Date 12/06/2017
how to make filter query so i can find free employee on that date..?
The result of query is James 12/07/2017 --- 12/09/2017
thx

[June7]

Should post your attempted query.

Looks like you want to find employee whose StartDate is on or before the job StartDate AND employee EndDate is on or after job EndDate. Construct WHERE clause accordingly.

None of the listed employees will meet that criteria.

[romi]

My query:


SELECT Schedule.ID, Schedule.[Employee Name], Schedule.[Start Date], Schedule.[End Date]
FROM Schedule
WHERE (((Schedule.[Start Date]) Not Between #12/04/2017# And #12/06/2017#) AND ((Schedule.[End Date]) Not Between #12/04/2017# And #12/06/2017#));

and result:


but smith still in this result..
i just want this result only James..

[June7]

These employee start and end dates represent period employee is NOT available?

SELECT * FROM Employees WHERE NOT [Employee Name] IN (SELECT [Employee Name] FROM Employees WHERE #12/04/2017# BETWEEN [Start Date] AND [End Date] OR #12/6/2017# BETWEEN [Start Date] AND [End Date]);


Advise no spaces nor punctuation/special characters (underscore only exception) in naming convention.

[romi]

Thank you Bro June7..
I combine this query with parameter..
so my query is:

PARAMETERS Start DateTime, Finish DateTime;
SELECT Schedule.ID, Schedule.[Employee Name], Schedule.[Start Date], Schedule.[End Date]
FROM Schedule
WHERE NOT [Employee Name] IN (SELECT [Employee Name] FROM Schedule WHERE [Start] BETWEEN [Start Date] AND [End Date] OR [Finish] BETWEEN [Start Date] AND [End Date]);


the result:

ID	Employee Name	Start Date	End Date
4	James	12/07/2017	12/09/2017

Thank you it solved bro...