Question

how do create a relationship in excel

Answer 1

Creating a table relationship by using the relationships window in excel

1. On the Database Tools Tab, go to the relationship group and in relationship group click on Relationships
2. If you have not yet identified any relationships yet, the SHOW TABLE dialog box shall automatically appear. If it does now appear, go to the Design tab, in the Relationships Group, please click SHOW TABLE. The SHOW TABLE dialog box displays all of the tables and queries in the database. To see only tables, please click TABLES. To see only QUERIES click QUERIES. To see both click on BOTH.
3. Select one or more tables or queries and then click ADD. After you have finished adding tables and queries to relationships tab, click CLOSE.
4. Drag a field (typically the primary key) from one table to the common field (the foreign key) in the other table. To Drag multiple fields, press CTRL key, Click each field, and then drag them.
5. We need to Verify that the field names shown are the common fields for the relationship. If a field name is incorrect, click on the field name and select the appropriate field from the list. Now we need to enforce referential integrity for this relations. Hence select the ENFORCE REFERENTIAL INTEGRITY check box.
6. Now after this click CREATE. Access draws a relationship line between the two tables. If you had selected ENFORCE REFERENTIAL INTEGRITY check box, the line will appear thicker at each end. In addition, again only if you selected the ENFORCE REFERENTIAL INTEGRITY check box, the number 1 appears over the thick portion on one side of the relationship line and infinity symbol appears over the thick portion on the other side of the line.

Additional -

• To Create a One to One relationship - Both the common fields (typically the primary key and foreign key fields) must have unique index. This means that the indexed property for these fields should be set to Yes (i.e. No Duplicates). If both the fields have unique index, Access will create one to one relationship.
• To Create one to many relationship - The field on the one side (typically the primary key) of the relationship must have a unique index. This means that the Indexed property for this field should be set to Yes(i.e. No Duplicate). The field on the many side should not have a unique index. It can have an index, but it must allow duplicates. This means that the indexed property for this field should be set to No or Yes (Duplicates ok). When one field has unique index, and the other does not, access creates a one to many relationship.