Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Designing new database to keep history

    Hello...I am trying to develop a database that tracks position numbers, names, and other fields in a database and I am having a hard time figuring out how to set up the database. What I want to do is assign the primary key to a position number, but this position number could change. I also need to keep the names of all the employees that have been assigned to a position number. For example position #1 had John from January 2014 to October 2014 and now position number #1 has Jason. I also need to track when a position number changes. For example position #1 became position number #15. I want a trail that can tell me that #15 used to be position #1. What is the most logical way to set this up in Access? Thank you in advance for all your help.

  2. #2
    Star Lounger
    Join Date
    Jul 2013
    Location
    Murphy, NC
    Posts
    66
    Thanks
    0
    Thanked 8 Times in 8 Posts
    Hi Seba,
    My immediate impression is that position number is too fluid to be a viable primary key. Instead, the primary key for position should probably be an immutable position_ID that the user might not ordinarily need to see. Whenever a given position_ID would be assigned a different position number, a tuple consisting (at minimum) of position_ID, position_number, and effective_timestamp could be entered into a Position Number History table. Analogously, there could be a table consisting of (at minimum) position_ID, employee_ID, and effective_timestamp, to which a row would be inserted whenever an employee were assigned a different position (but not if the change were merely to the position_number associated with the employee's present position_ID).

    Presenting the data as if they were keyed by position_number rather than position_ID would require creating "views" (or whatever they're called in Access) that would join tables by position_ID and involve other criteria (such as the most recent row in Position Number History for the given position_number).

    Hope this helps,
    Dave

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. I will give it a try.

Posting Permissions

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