Table - Relations

Launches relations dialog to create and manage relations between two tables. Relations are connections between two tables that allow one table to show columns from another table. A relation between tables uses a key field with unique values common to both tables to connect the two tables. This allows data that appears in one table to also appear in another table without having to physically duplicate the data. The Relations dialog is resizable.


To form a relation between two tables


1. Open the table that is to display the additional fields and click on Table - Relations.

2. images\btn_new_thing.gif Click on the New Relation button. This launches the Add Relation dialog.

3. In the Add Relation dialog, choose another table from the list box.

4. In the Add Relation dialog, click on one field for each table that will be used to match records and press OK.

5. Back in the Relations dialog, check the desired columns from the other table. Press OK.


Columns that are "borrowed" from another table will appear in the table with yellow background color to indicate they are imported. They may be used like any other column, for example, for sorting, filtering or within formulas or thematic formatting. Tables may have more than one relation with more than one other table.


Columns that are included from other tables may not be used as a key field to form a new relation. Columns can only be linked through one relation. They cannot be passed on in turn through yet another relation. For example, if table A has a relation with table B so that columns B1 and B2 appear in table A, table C cannot form a relation with table A and "borrow" columns B1 and B2 from table A. To include columns B1 and B2 in table C, table C must form a relation directly with table B.


Relations Dialog Commands



New Relation - Add a new relation.


Delete - Delete highlighted relation.


Include All - Show all columns from the related table in this table. Check all checkboxes.


Include None - Do not show any columns from the related table in this table. Uncheck all checkboxes.


Include Inverse - Uncheck all checked columns and check all unchecked columns. A fast way to show all but one column: click Include None, check the one column not desired and then click Include Inverse.


Properties - Edit the highlighted relation.




Relations are used to add columns to tables from other tables.



Suppose we have a Customers table with a CustomerID field and a ContactName field.



Suppose also we have an Orders table with OrderID, CustomerID and an OrderDate field.


We would like to create a relation that shows in the Customers table the OrderID and OrderDate columns so that for each customer we can see all orders for that customer as well as the date of order. To do this, we open the Customers table and choose Table - Relations.


images\btn_new_thing.gif In the Table Relations dialog we click the New Relation button to add a relation. This opens the Add Relation dialog.




In this dialog we choose the Orders table in the list box and then highlight CustomerID as the linking field in both the Customers table (the left pane) as well as in the Orders table (the right pane, under the list box showing the Orders table). Note that we could have used any two fields that contain matching values even if they are named differently. However, as a matter of sensible database organization it makes sense to give the same name to the field used to save customer identification in all tables. We press OK.




The result back in the Relations pane is that we have a new relation listed as CustomerID: Orders.CustomerID. This is a shorthand way of saying that this relation is determined by matching values in the CustomerID field in this table to the CustomerID field in the Orders table.


We check the fields (columns) we would like to include from the Orders table and press OK.




The result in the Customers table window is that two new columns appear, the contents of which are taken from the Orders table. These contents are shown in yellow, which is a generic background color used to indicate values that are computed or otherwise derived.




A table may have more than one relation. For example Table A might include two columns, B1 and B2, by way of a relation with Table B and Table A might also simultaneously include one more column, C1, by way of a relation with Table C.


Relations can exist between any tables that exist in the project. In particular, a drawing's table can have a relation with another table.


Relations can exist between tables that are imported into a project and tables that are linked into a project.


When including fields from tables that are linked into a project, keep in mind that tables linked into a project may be provided by files or OLE DB providers that might be participating in multi-user sessions with other programs. That's usually the objective of including such external tables, of course, but one should not be surprised if data in columns that are included from external tables appears to change without user intervention.