# Join to Specific Record for Calculation

Question asked by stevestearns_1 on Dec 16, 2008
Latest reply on Dec 20, 2008 by stevestearns_1

### Title

Join to Specific Record for Calculation

### Post

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:

contactID

firstName
lastName

...

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:

contactID

relationID

firstName

lastName

relationship (see list above)

birthday

anniversary

...

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.