Creating new record from relationship
I am working on a database that is used to manage faculty, my question involves the use of 3 tables: Personnel, Jobs, and Position History. There are two unique keys i'm using in these tables, an Employee ID and Position Number. Some faculty have multiple positions, so I have a jobs table for listing their positions. The position history will have be a record of who has held the position previously, so I'm using the position number as the key.
In each personnel record, there is a portal that lists each faculty members "jobs". I've allowed the creation of records through this relationship, so I can add jobs as needed. What i'd like to do, is be able to add a new job, using the position number, job title, etc, that will then create a record in the "position history" table, so I can capture the history of a position, using the position number. Simply put, when I enter a "job" into someone's record, i want a new record to be created in the "position history" table that will include the position number, the employees first & last name, employee ID. This way I can run a report that will show historically who has held this position, but not have to enter this information into both the "Jobs" table and "Position History" table. i'd like to make this automated, so I only have to enter it once, then the data is carried over to the appropriate fields.
Hopefully these instructions are pretty straightforward. If anyone could give me some advice on how to make this work, I would greatly appreciate it. Thanks for your time.