Okay. Probably a very newbie question.
In our post production business we have SESSION's. The SESSION includes a list of AGENCY CONTACTS that were involved. These CONTACTS are referenced from a related table, and once an AGENCY is selected in one field, only the AGENCY CONTACTS from that particular agency will be selectable in the other fields.
The problem is, these contacts move from one agency to the next quite frequently.
So in a month, when 'Jill Smith' moves to a new agency, her populated field in all previous records disappear - since once I update her AGENCY CONTACTS records to a different agency, her record cannot be referenced by the older sessions anymore.
I my mind my options are:
1. LOCK that particular field after the SESSION date has occurred, since there is no chance of this field changing after that.
2. Create a new record for Jill Smith at her new agency and leave the out of date record active (which means her name would show up as an option in future sessions with her old agency - don't like this option)
3. Re-structure the way these fields reference the CONTACTS.
Thank you for any comments and ideas. I am a complete newbie, so dumb it down as best you can.