Query Duplicate Records & Dates

#1 (**permalink**) 28-Jul-2006, 08:32 AM

Hi,

I have a table of employees' history of start and term dates. Start and
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12790-query-duplicate-records-and-dates.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12790
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!

*

#2 (**permalink**) 28-Jul-2006, 08:32 AM

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

"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
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12790
> 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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12790
>
> Version: Access 2003, Professional
>
> Appreciate the help, Thanks!

#3 (**permalink**) 28-Jul-2006, 08:32 AM

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

"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
> 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.
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12790
> 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!

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4182 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12790