Using multiple key fields in combination

If the values in a single common key field are insufficiently unique to accurately join or relate two tables, you need to use multiple common key fields in combination. For example, to capture and correctly match all records for a vendor with outlets at more than one location, you might need to use both vendor ID and location fields as key fields. If you join or relate tables using only vendor ID, secondary or child table records with anything other than the first listed location are not included in the joined table (assuming a many-to-one join) or the related table, and locations are erroneously matched between tables. To capture all vendor locations, and ensure proper location matching, you need to use both the vendor ID and the location fields as key fields in both tables. When combined, the values in each field form a single unique value that can be used to reliably match records between the two tables. Figure 1 and Figure 2 illustrate the difference.

Figure 1. Single common key field

Figure 2. Multiple common key fields in combination

Using multiple key fields in combination when joining tables

When you join two tables, you can select more than one key field in each table, so using multiple key fields in combination when joining tables is straightforward. Restrictions involving data type, field length, justification, and case that apply when using one key field still apply to the corresponding key fields in each table when using multiple key fields. Within a table, the multiple key fields can be of different data types if required – for example, first name, last name, and date of birth.

Selecting more than one key field in the Join dialog box creates a nested sort order in the output table, assuming you Presort the primary table while performing the join. The order in which you select the key fields dictates the priority of the nested sort order.

An alternative method when using multiple key fields to join tables is to create a computed field in each table that concatenates the required key fields, and join the tables using the computed field.

Using multiple key fields in combination when relating tables

When you relate tables, you can select only one key field per table pair, so you need to employ one of the following methods to use multiple key fields in combination:

The requirement that common key fields have an identical data structure and data format also applies to concatenated key fields, and newly created fields that encompass multiple key fields.

Related concepts
Using multiple key fields in isolation
Using multiple key fields
Related tasks
Defining physical data fields
Concatenating key fields

(C) 2015 ACL Services Ltd. All Rights Reserved.