Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2011
    Posts
    27

    Unanswered: Date Lookup in Query for Junction Table

    My goal is to have a list of employees and their current position, and to be able to retrieve an employee's job title at a point of time in the past. i.e. What is Joe Smith? He is a supervisor. What was he on 1/1/2004? He was an intern.

    The complicator is that I have a junction table called PeoplePositions that connects People and JobTitles. Different people can have different job titles at different times.

    PeoplePositions has its own primary key, a PeopleFK, and a JobTitlesFK as well as DateStarted and DateEnded.

    Example data showing two employees, one that was promoted twice and another that was promoted once. Both are still employed.

    PK PeopleFK JobTitlesFK DateStarted DateEnded
    1 1 5 1/1/2001 12/31/2001
    2 1 6 1/1/2002 12/31/2002
    3 1 7 1/1/2003
    4 2 10 6/1/2001 8/15/2002
    5 2 13 8/16/2003

    I would like to find the current job (really just the PK):
    PeopleFK PK
    1 3
    2 5

    and for a particular date like 1/10/2002:
    PeopleFK PK
    1 2
    2 4


    One question is: What should be in DateEnded when someone is still employed? I am not sure how to get the current date there if it's needed.

    I can find the position of one employee at a certain time in Query Design as follows:

    CurrentPosIDtest2: Nz(DLookUp("EmployeeAndPositionID","PeoplePosition s","[DateStarted]<#1/4/2007# AND [DateEnded] > #1/4/2007# AND EmployeeFK=2"),"")

    My burning question is: How do I get a dynamic list?

    I hope I provided all the necessary information. Help is greatly appreciated!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    NULL seems good - it accurately reflects the DateEnded value as "undefined".

    Code:
    -- current position
    SELECT p.PeopleFK, p.PK
    FROM PeoplePositions p
    WHERE p.DateEnded IS NULL;
    
    -- people:position on <somedate>
    SELECT q.PeopleFK, q.PK
    FROM PeoplePositions q
    WHERE q.DateStarted <= somedate
    AND (q.DateEnded > somedate   OR   q.DateEnded IS NULL);
    the choice between <, <= etc is quite interesting: maybe have a look at this.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2011
    Posts
    27
    Your code worked perfectly, izy. Thank you!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •