Hi all,
I have a table that serves as a team roster (Table 1), which specifies the area that each team member is in charge of. Then, I have another table (Table 2) as a view of Table 1 above. Table 2 has info about all projects of my team. Right now, Table 2 has 3 columns: project, area, team member. For each project, when we select its area, the team member in charge will be automatically populated with reference to table 1.
Now, when I update Table 1, Table 2 will be automatically updated, but sometimes I don’t want it to be. For example, in March, Jen is responsible for Ops area, so she is responsible for project A – an Ops project. In April, due to reorg, Linda is now responsible for Ops area, and Table 2 will automatically update Linda as the point of contact for project A. However, I still want to keep Jen as POC for project A.
Is there a way to keep point-in-time data like that? In other words, I want every old Ops projects that Jen worked on to be under Jen’s name forever (for accountability purpose), and every new Ops project to be specified automatically under Linda because Linda is now managing the Ops team as updated in the roster in Table 1
Thank everyone!