Relating tables

Using a common key field from each table pair, you can relate two or more ACL tables with different record structures. Once tables are related, you can use the parent table to access and analyze data from any combination of fields in the related tables.

Note

Carefully identify your parent and child tables because results can differ if you reverse the order. For more information, see Common uses of joining or relating.

To relate tables:

  1. Open the parent table (primary table) and select Data > Relate Tables.
  2. In the Relations dialog box, click Add Table and select one or more child tables (secondary tables).

    You can relate up to 18 tables, including the parent table.

  3. Click Add and then Close.

    You can also double-click a child table to add it singly.

    To remove a table from the Relations dialog box, right-click the body of the table and select Remove Table.

  4. Do one of the following:
    • Drag the key field from the parent table to the corresponding key field in the child table.

    • If the child table has no existing index on its key field, and you want to specifically name the child table index auto-created by ACL during the relating process, right-click and drag the key field from the parent table to the corresponding key field in the child table. Select Relate using Named Index, specify a name for the index, and if desired a location other than the default (the folder containing the ACL project), and click OK.

    An arrow appears between the two key fields, indicating the relation between the tables.

    The key fields can be any data type, but for each table pair they must be the same data type. Datetime subtypes (date, datetime, and time) can only be related to the same subtype. It is recommended that key field lengths be identical for each table pair. Key field names and start positions can be different, but they must describe the same data element.

    If the child table has two or more existing indexes on its key field, you are presented with a list of these eligible indexes. Select the appropriate index and click OK.

  5. Relate any additional tables in the same manner as the first table pair, by dragging key field to key field.

    Each additional relation must create a direct or indirect link to the parent table.

    Note

    Individual instances of two tables can have only one relational association. If you try to relate the same table pair a second time, the operation is prohibited and an error message appears. Add another instance of the required table by clicking the Add Table button and selecting the appropriate table.

    For more information, see Using multiple key fields in isolation.

  6. If required, right-click the working area of the Relations dialog box and select Arrange Tables to neaten the arrangement of tables and relation arrows.
  7. Click Finish to exit the Relations dialog box.

    At this point you can proceed with accessing and analyzing data from any combination of fields in the tables you have just related, as if all the fields existed in a single table. When accessed from the parent table, the From Table drop-down list in ACL dialog boxes and the Expression Builder allows you to select the individual related tables from which you can then select individual fields. There is no requirement that you add child table fields to the parent view, although you may find that doing so helps you better visualize the related data.

  8. If you want to add child table fields to the parent view, do the following:
    1. Right-click the parent view and select Add Columns.
    2. Select a child table from the From Table drop-down list.

      In the Available Fields list, child table fields appear in the format child table name.field name.

    3. Select one or more child table fields to add to the parent view.

      Child table fields appear in the parent view in the order in which you select them.

    4. If applicable, select additional child tables from the From Table drop-down list, and select additional child table fields to add to the parent view.
    5. Click OK.

      The child table fields are added to the parent view. ACL fills missing values in child table fields for unmatched parent table records with blanks or zeros.

Related concepts
Using multiple key fields
About relating tables
How table relations are structured
Related tasks
Modifying relations
Concatenating key fields


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