Hi,
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')
Thank you.
