1 Reply Latest reply on Apr 16, 2012 11:38 AM by philmodjunk

    Creating a copy of a record in a different table



      Creating a copy of a record in a different table


      Hi all,


      So I have a DB with a table for employee data (wage,hours,benifits, SS....) when that employee gets a raise of a change to his/her data I want to have the current record moved to a new table as an archive of the employees history with the company and then edit the original table's record for the employee since I have several other tables pulling info from it.  I thought about a simple "lookup" for each of the fields but is there a way to prevent that data from ever being refreshed in error?


        • 1. Re: Creating a copy of a record in a different table

          Take your pick:

          1. You can use Import Records instead of a look up.
          2. You can use two fields for the Employee ID with separate relationships for each field. Use one for the look up and then clear it. Use the other to maintain a link to the Employee table. You can also use record level access control to "lock" individual records against change when the DB is opened by any but a full access user.
          3. Don't use this method at all. Define a separate "Wages" table related by employee ID. Create a new record (you can duplicate the existing one) each time their wage info changes. Use an "effective date" field to identify which record for a given employee is the current record for a given employee. (You can sort the relationship by this date in descending order and then the first related record will be the most recently dated one.)