Store looked up values into field repetitions?
I have a table of people and another of marriages. The marriages is basically a pairing of the people (ie, there are two fields, one for the first person, and one for the second), that has a unique serial number associated with it. I have the people table related to the marriages table through each of these two fields, so it's something like the following:
People Table: Person ID --> Marriages: Person 1
People Table: Person ID --> Marriages: Person 2
With these two relationships, I'm looking up the marriage id from the Marriages table to have it present on each person's record in the People table.
This works; however, at times someone may have switched partners so the marriage ID changes. This updates properly; however, I would like to have a way to automatically preserve the prior marriage for that person, and have this prior marriage be searchable along with the first one.
The marriages table can support Person 1 being with Person 2, and then having a new entry that has Person 1 with Person 3, etc., so the data of the multiple marriages is there; however, the problem comes with displaying all of these pairings for Person 1 in the record for Person 1.
So far I have a calculation filed that attempts pulls the marriage IDs to the People records, and it works; however, it only pulls the first-found marriage. So for instance, with Person 1 having been married to Person 2, this is the marriage ID that shows from the calculation, and not the second one.
I would like somehow to pull both by calculation into Person 1's record. I have tried creating a repeating field for the Marriage IDs in the People table so it can hold multiple values, but cannot seem to get the calculation that finds the marriage IDs to either 1) find multiple values, and/or 2) store them in the separate field repetitions.
Is there a way to do this, or another approach altogether that, given this scenario, will be able to look up the multiple Marriage records containing Person 1, and then store the various IDs in Person 1's "People" record?