Join to Specific Record for Calculation
I am trying to create some calculations based upon a join to a specific record in another related table (i.e., spouse or partner). I know how to relate (or join) the two tables, but am not sure how to relate to a specific record within the related table to create the calculation.
The first table will contain all the contact information for the primary person, so we’ll call this table ‘Contacts’. It will have the following fields:
The second table will contain information about relationships to the primary person, for instance: spouse, partner, child, friend, assistant, father, mother parent, brother, sister, manager, etc. The fields for the ‘Relations’ table would contain:
relationship (see list above)
The two tables would be joined or related by Contacts: contactID = Relations: contactID.
Now, how to do contact records that match relationship equal to spouse or partner? What I would like to be able to do is:
Contacts record = John Doe
Relations record = Jane Doe (spouse) or Jane Smith (partner)
Calculated result =
John and Jane Doe (if last names are the same and relationship = spouse)
John Doe and Jane Smith (if last names are not the same and relationship = spouse or partner)
I know how to build complex If or Case statements, so all I need help with is how to use a specific record in the calculation, because a primary person can have multiple relationships, spouse, child, etc.