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!