Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Query Duplicate Records & Dates

Discussion in 'Information Technology' started by sgyvln, Jul 28, 2006.

  1. sgyvln

    sgyvln
    Expand Collapse
    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!
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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" <sgyvln@discussions.microsoft.com> 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.
    >
    > 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!
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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" <sgyvln@discussions.microsoft.com> 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.
    >
    > 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
     

Share This Page