A starter would be
SELECT E.EMPLOYEEID, -1 AS OCCUPIED FROM EMP E
WHERE E.START >= DATE() AND E.TERM <= DATE()
SELECT E.EMPLOYEEID,0 FROM EMP E
WHERE NOT EXISTS (SELECT 'X' FROM EMP AS E2
WHERE E2.EMPLOYEEID= E2.EMPLOYEEID AND START >= DATE() AND E2.TERM <=
DATE()) Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12790-query-duplicate-records-and-dates.html
"sgyvln" wrote in message
> I have a table of employees' history of start and term dates. Start and
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12790
> Term Dates constantly changes so there will be several records attached to
> the employee. I need to create a query listing the current employee who
> working and another one listing employees who are no longer on assignment.
> How do I extract just the most recent dates and ensure that there are no
> duplicate names in both list?
> Version: Access 2003, Professional
> Appreciate the help, Thanks!