Many-to-Many Relations

This type of relationship presupposes multiple connections of records in first table with records in the second table.

Example 1

Let’s assume you manage a Design Service Agency and calculate monthly payments to each designer according to the hours they worked. In this case a Many to Many relation between the "Hours Worked" table and the "Monthly Payments" table can be helpful.

In the most of cases a Many to Many relation comprises Match Conditions. For example, there is the "Designer" column in the "Hours Worked" table and the "Monthly Payments" table. This column comprises Designer Id value. As a result, Designer=Designer match condition can be added to the relation. After that the "Total Hours" summary column can be added to the "Monthly Payments" table, where the total number of designers’ worked hours is calculated.

Example 2

Let’s say your Health Insurance Claims database includes two tables "Customers" and "Claims" keeping customers’ profiles and registered claims respectively. Moreover, customers submit claims by web-to-record form imbedded into your website. Therefore in your database you need to relate submitted claims to a corresponding customer profile automatically using an Email kept in customer profile and an Email specified in submitted claim.

Firstly, you need to build a Many-to-Many relation between the "Customers" table and the "Claims" table.

On the next step, you add the match condition Email=Email to the relation settings and choose a details view.

As the result, claims are related to a customer by Email value. Also, you can see such claims in the details view displayed below a Customer record form.

Please notice that the match condition Multi-reference Contains Single-reference can be used only in Many-to-One relation. It does NOT apply for Many-to-Many relation.