Results 1 to 3 of 3
  1. #1
    Phasma is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2011
    Location
    Indianapolis
    Posts
    2

    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. Any help is greatly appreciated!

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    well you don't need a DateEnded for their current position; you can search on IsNull to find everyone's current position.

    beyond that it is a simple date style query conceptually just this....
    DateStarted>=SoughtDate AND DateEnded<=SoughtDate
    OR
    DateStarted>=SoughtDate AND IsNull(SoughtDate)

    how you actually implement that will depend on your table structure

    hope this helps.

  3. #3
    Phasma is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2011
    Location
    Indianapolis
    Posts
    2
    Search for null, that's a good idea. For some reason I thought I needed a dlookup. I'll try a simple query like I should have first and let you know how it goes.

    Thanks!

    Quote Originally Posted by NTC View Post
    well you don't need a DateEnded for their current position; you can search on IsNull to find everyone's current position.

    beyond that it is a simple date style query conceptually just this....
    DateStarted>=SoughtDate AND DateEnded<=SoughtDate
    OR
    DateStarted>=SoughtDate AND IsNull(SoughtDate)

    how you actually implement that will depend on your table structure

    hope this helps.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Junction Table
    By mae3n in forum Database Design
    Replies: 2
    Last Post: 01-15-2011, 10:23 PM
  2. Replies: 6
    Last Post: 08-27-2010, 07:06 PM
  3. Yes/No in query lookup
    By eww in forum Queries
    Replies: 1
    Last Post: 08-24-2010, 10:24 AM
  4. Confused about lookup in table v form
    By jray7000 in forum Access
    Replies: 3
    Last Post: 08-23-2010, 03:36 PM
  5. Lookup Table For Listbox Input
    By ColPat in forum Access
    Replies: 1
    Last Post: 05-26-2010, 05:09 AM

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
  •  
Other Forums: Microsoft Office Forums