In relation to your from/To, there are 6 possible employee states - hope this layout makes sense to you
..
.|From...
|To
Emp1...Hire....Term
Emp2.....Hire...
...Term
Emp3...
Hire.....
.
Term
Emp4...
.Hire......Term
Emp5...
Hire...
..Term
Emp6...
..Hire......Term
there are 3 others, where the contract has not got a termination date
Emp7...
Hire.....
.
..
Emp8...
.Hire...
...
Emp9...
..Hire...
.
The basic principle to determine whether to include an employee is hiredate is before/equal To and termdate is after/equal From. In the case of no term date and assuming you want to include them, substitute with the from date using the nz function
First I need to count the number of records hired before a specific hire date, removing any records that have been terminated before a specific term date. EX: I want every employee who was hired before and terminated after 1/1/2018.
So relating to the above, From and To are the same date, so you would only want Emp3 - and perhaps Emp7 if you want unterminated employees
If you wanted to know how many employees were employed in Jan 18, the formula would return Emps 2,3,4,5,7 and 9
So your basic query would be something like
Code:
SELECT *
FROM tblEmps
WHERE nz(Term,[Enter From Date])>=[Enter FROM Date] AND Hire<=[Enter End Date]
you can take it from here to refine for your actual requirements