Stop a lookup field being modified in the child table when it is modified in the parent table?
How can I stop a lookup field being modified in the child table when it is modified in the parent table?
I have two tables, Talent and Assignments (a one-to-many relationship). One Talent can have many Assignments. So for example, I have a freelance photographer called Joel in Talent. This table includes all the information about Joel - his email, phone, agency and also his daily rate.
When I add Joel to a job in Assignments it looks up fields from Talent and adds them to fields in Assignments. Among these is his daily rate so I know how much we paid Joel on that job.
My problem is that if Joel decides he wants to charge more money going forward, and I modify Joel's rate in his Talent record, in Assignments it now changes his rate in every job he's ever done because it's just looking up his daily rate from Talent. I don't want it to do this because what we paid him in the past must obviously reflect what his rate was then, not what it is now.
What is a way to handle this? Not having any luck so far.
I have two tables, Talent and Assignments (a one-to-many relationship). One Talent can have many Assignments. So for example, I have a freelance photographer called Joel in Talent. This table includes all the information about Joel - his email, phone, agency and also his daily rate.
When I add Joel to a job in Assignments it looks up fields from Talent and adds them to fields in Assignments. Among these is his daily rate so I know how much we paid Joel on that job.
My problem is that if Joel decides he wants to charge more money going forward, and I modify Joel's rate in his Talent record, in Assignments it now changes his rate in every job he's ever done because it's just looking up his daily rate from Talent. I don't want it to do this because what we paid him in the past must obviously reflect what his rate was then, not what it is now.
What is a way to handle this? Not having any luck so far.
