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.
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:
In each table, create a computed field that concatenates (adds together) two or more key fields, and relate the tables using the computed fields.
You can concatenate only character key fields, so you may have to use ACL functions to convert non-character data prior to concatenating.
In each table, add a new field long enough to encompass the data in the multiple key fields, and relate the tables using the new field.
This method works only if the multiple key fields are adjacent in each table. Fields can be made adjacent by extracting by field to a new table and selecting the fields for extraction in the required order. New fields that encompass multiple key fields can be any data type supported by the source data. If the multiple key fields are of different data types, you can create the new field encompassing them as a character field because you are using it only for the purposes of relating tables.
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.