A starter would be
SELECT E.EMPLOYEEID, -1 AS OCCUPIED FROM EMP E
WHERE E.START >= DATE() AND E.TERM <= DATE()
UNION ALL
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
HTH
Pieter
"sgyvln"
wrote in message
news:7F586B63-466D-4221-BB60-C5E67F72FBCB@microsoft.com...
> Hi,
>
> 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
> is
> 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!