I have 2 tables, Department & Sales that look like this:
Department
Department
Employee
StartDate
EndDate
Counter
Paul
01/02/2013
23/12/2013
Counter
Ringo
17/06/2011
Present
Counter
John
24/11/2010
11/03/2014
Sales
George
03/08/2012
28/12/2012
Sales
Brian
11/06/2013
Present
Sales
Chuck
21/10/2012
28/02/2013
Sales
Department
SaleID
SaleDesc
SaleDate
SaleAmount
Counter
135
Watch
21/07/2012
1000
Sales
203
Neck
10/01/2014
2050
Counter
107
Wed Ring
15/04/2011
7000
When a selection is made like Watch, all the Department employees for Counter are displayed. Is there any way to only show the employees that were working in a department at the time the item was sold (in this case John & Ringo)? ie. if the SaleDate>=StartDate AND (SaleDate<=EndDate OR SaleDate='Present')
