Query Duplicate Records & Dates

[Guest]

Hi,

I have a table of employees' history of start and term dates. Start and
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!

 

[Pieter Wijnen]

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())

HTH

Pieter

 

[Pieter Wijnen]

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())

HTH

Pieter

Hi,

I have a table of employees' history of start and term dates. Start and
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!

--