Is there any way to link two separate clients?

[I Need Help]

Hi all,
uber noob has another question!
Ok so I'm creating this database for a claims management company. Yesterday I learnt about making a many to one relationship between the claims and clients tables.
Turns out its more complicated than that. Since I'm dealing with claims, a person may add their partner as the second claimant. (usually the case in mortgages)

So where parties A and B are in a relationship:
person A may claim on their own
Person B may claim on their own
Person A may be the primary claimant and add person B
or conversely person B may be the primary claimant, adding person A.

The end goal is to automatically fill in a financial form (making use of a mail merge).
So from what I know I think this means:
that grouping them as one contact is not going to work as I need them to be able to do a solo claim, and also I will not be able to easily change around who is the primary or secondary claimant, which will then carry over onto the mail merge.

So I think this means I need them as two separate contacts. However, I don't know how to create a link between them to easily add their partner to a claim.

Would this also mean I need a many to many relationship between clients and cases.
Whew, this one's difficult enough to explain, never mind solve! I'm pretty stumped! Any help would be greatly appreciated
Regards
Sam

 

[CJ_London]

You need a separate table (we'll call it LinkTbl) with the following fields

ClaimantID - Long
Status - Text
ClaimID - Long

Status is A or B (or C or D...)

Then link claimants to LinkTbl and LinkTbl to Claims. Drop any direct relationships between Claimant and Claim.

So if you 'call up' a claim you will see both claimants (or one or three depending on how many records are in the LinkTbl)

Similarly, if you open a Claimant you will be able to see the claim and any other claimants

 

[I Need Help]

Hi CJ_London,
Thanks for your reply. So essentially I create a link table in order to have a many-to many relationship?

2 things about that though:
1 how will the db know to group parties A and B as husband and wife
2 in theory this means I can add more than 2 people to a claim.

Also would you be able to further explain what the purpose of the status field is
Regards
Sam

 

[CJ_London]

1 how will the db know to group parties A and B as husband and wife

They won't but in the link table Status could Husband, Wife, Spouse, Father etc, instead of A or B - you would presumably know this when the recordsa re being completed. Alternatively you may need a separate field(s) in the Link table to indicate primary/secondary or percentage split etc

2 in theory this means I can add more than 2 people to a claim

True!

Also would you be able to further explain what the purpose of the status field is

It is not actually required per se, I only included it because of the way your post was phrased, but as it turns out, looks like you need it per above anyway!

 

[I Need Help]

Hi, thanks for your reply,
To be fair, the db doesn't need to know the nature of the two parties' relationship, just that there is one. Therefore either party could be the sole claimant, or add their partner as the secondary claimant (ideally at the click of a button). Their partner should be the only person who can be added to the claim with them. Also, I need the number of claimants on one claim to be limited to two individuals. The reason this is so important is that I am ultimately using the database to fill in a mail merge to an already existing official document (a financial questionnaire) where this matters.
Any ideas?
Thanks for your help
Regards
Sam