storing historical data in Access

[megbowlstrike]

Hi. I am creating an Access database to store information about
employees, such as their job title and address info. However, I also
need to store their historical job title and address info as it
changes. I have other tables in the database to store info on their
different benefit plans. In the employee info table, I have their
Employee ID (the number that the company assigns to each employee) as
the primary field. So, it will not let me add duplicate entries for
each employee. I thought about making the primary key an autonumber
field but I need the primary key to be the employee ID so the other
tables can be linked to this one. Is there any way I can have Access
store the previous records from a table and output it in a report
format? Or is there a better way to do this that I'm not thinking
of? I used to be very good with Access but haven't used it for a
couple years, so I'm trying to re-familiarize myself with it again.

Thanks,
Megan

 

[Douglas J. Steele]

You need a compound key: EmployeeID and EffectiveDate.

You do this by select as many fields as you need for the primary key (up to
ten), and then clicking on the Key button to set the Primary Key.