1 Reply Latest reply on Feb 18, 2015 1:01 PM by philmodjunk

    Creating new record from relationship



      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.


        • 1. Re: Creating new record from relationship

          It would seem that your jobs and position history tables are very similar, perhaps so similar that the same table might store both records--and that would eliminate the need to create a matching record in Position History every time that you create a new record in Jobs. The different between current and past jobs might be no more than a single field that identifies which jobs are "current" or some such designation.