Ability to archive certain fields without creating a new/separate record?
I'm a new Filemaker Pro 10 user but I have been learning very quickly (I think). I'm trying to develop a solution that tracks data associated with an administrative hearing system. I'm currently having trouble deciding how to structure my solution to accurately track information about certain entities. Specifically, I'd like to know whether it's possible to archive some aspects/fields of a given record without creating a new record.
For example, "Company A" has a CEO named "Joe Smith" in 2009 and Joe Smith's name gets recorded in the record associated with Company A today. Now, let's say I have new event in 2011, but Company A's CEO in 2011 is now "Bob Jones". I would want to be able to accurately record the 2011 event with Bob Jones as CEO of Company A. But at the same time, if I go back to the 2009 record, I'd still want that record to reflect that Joe Smith was CEO in 2009, while still keeping the Company A data as a single record (as opposed to two records). I need to do this so that I can accurately find all the occurrences of Company A in the database, but still be able to know who was running Company A at any given point in time.
I've been thinking about setting up the CEO names as a separate table and then relating the CEO table back to the table with the Company data. However, I have a nagging sense that this won't work and I'll still wind up with duplicate records for the same company.
Is it possible for me to achieve what I'm describing here? I'm sorry for my lack of expertise in describing the problem more clearly, but any help or ideas would be appreciated.