Using multiple key fields in isolation

If the values required to join or relate two tables are split between two (or more) key fields in one of the tables being joined or related, you need to use these multiple key fields in isolation when joining or relating the tables. For each of the multiple key fields you perform a separate join operation, or form a separate relational association, in a process that yields a unified final result.

Two examples illustrate the situation, one with the multiple key fields in the secondary or child table, and the other with the multiple key fields in the primary or parent table:

In either of these examples, if you join or relate the tables using only one of the key fields in the tables with multiple key fields, the resulting data is incomplete. The sections that follow explain how to join or relate the tables using the multiple key fields in isolation to produce a complete set of data.

Using multiple key fields in isolation when joining tables

To use multiple key fields in isolation when joining tables, you perform successive joins with each join using only one of the multiple key fields. You use the output table from the first join as the primary table in the second join, and so on. When performing each join you must select the join type that includes matched and unmatched primary records (that is, all primary records) so you do not lose the unmatched primary records at any point in the process. With each join you must also include in the output table any primary table fields that are required as key fields for subsequent joins or that you want to appear in the final results.

Note

The figures that follow illustrate only the key fields in the tables being joined. Typically, tables also include other data in non-key fields.

Figure 1. Using multiple key fields in isolation when joining tables (Example 1)

Figure 2. Using multiple key fields in isolation when joining tables (Example 2)

Using multiple key fields in isolation when relating tables

To use multiple key fields in isolation when relating tables, you add an additional instance of the child table for each additional relation between the single key field and one of the multiple key fields. You add additional instances of the child table by clicking the Add Table button in the Relations dialog box and selecting the appropriate table.

Figure 3. Using multiple key fields in isolation when relating tables (Example 1)

Figure 4. Using multiple key fields in isolation when relating tables (Example 2)

Related concepts
Using multiple key fields in combination
Using multiple key fields


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback