34) In Microsoft Access, any relationship between two tables is created: A) by entering the name of the foreign key in the appropriate table in Design View. relationships between your tables so that Access can bring related information from .. example: Mr OR Mrs OR Ms OR Miss OR Dr. . between the two tables in a similar way to creating relationships between tables in the Relationship. established between common fields (columns) in two tables. . When you close the Relationships window, Microsoft Access asks if you want to save the layout.
Create, edit or delete a relationship - Access
The relationship line appears thicker when it is selected. With the relationship line selected, double-click it. The Relationships window appears. If you have not yet defined any relationships and this is the first time you are opening the Relationships window, the Show Table dialog box appears.
If the dialog box appears, click Close. On the Design tab, in the Relationships group, click All Relationships.
How to define relationships between tables in an Access database
All tables with relationships are displayed, showing relationship lines. Note that hidden tables tables for which the Hidden check box in the table's Properties dialog box is selected and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box. Click the relationship line for the relationship that you want to change. Double-click the relationship line.
On the Design tab, in the Tools group, click Edit Relationships. Make your changes, and then click OK. The Edit Relationships dialog box allows you to change a table relationship. Specifically, you can change the tables or queries on either side of the relationship, or the fields on either side. You can also set the join type, or enforce referential integrity and choose a cascade option. For more information about the join type and how to set it, see the section Set the join type.
For more information about how to enforce referential integrity and choose a cascade option, see the section Enforce referential integrity. Set the join type When you define a table relationship, the facts about the relationship inform your query designs. For example, if you define a relationship between two tables, and you then create a query that employs the two tables, Access automatically selects the default matching fields based upon the fields specified in the relationship.
You can override these initial default values in your query, but the values supplied by the relationship will often prove to be the correct ones.
Because matching and bringing together data from more than one table is something you will do frequently in all but the most simple databases, setting defaults by creating relationships can be time saving and beneficial. A multiple table query combines information from more than one table by matching the values in common fields. The operation that does the matching and combining is called a join. For instance, suppose you want to display customer orders. The query result contains customer information and order information for only those rows where a corresponding match was found.
One of the values you can specify for each relationship is the join type. The join type tells Access which records to include in a query result. For example, consider again a query that joins the Customers table and the Orders table on the common fields that represents the Customer ID.
Using the default join type called an inner jointhe query returns only the Customer rows and the Order rows where the common fields also called the joined fields are equal. To accomplish this, you have to change the join type from an inner join to what is known as a left outer join. Access can then use the Customer ID number in the Orders table to locate the correct customer for each order. A single order can include more than one product. On the other hand, a single product can appear on many orders.
Therefore, for each record in the Orders table, there can be many records in the Products table. In addition, for each record in the Products table, there can be many records in the Orders table. This relationship is called a many-to-many relationship. Note that to detect existing many-to-many relationships between your tables, it is important that you consider both sides of the relationship. To represent a many-to-many relationship, you must create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships.
You insert the primary key from each of the two tables into the third table. As a result, the third table records each occurrence, or instance, of the relationship. For example, the Orders table and the Products table have a many-to-many relationship that is defined by creating two one-to-many relationships to the Order Details table.
One order can have many products, and each product can appear on many orders. A one-to-one relationship In a one-to-one relationship, each record in the first table can have only one matching record in the second table, and each record in the second table can have only one matching record in the first table.
This relationship is not common because, most often, the information related in this way is stored in the same table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table.
When you do identify such a relationship, both tables must share a common field. Top of Page Why create table relationships?
You can create table relationships explicitly by using the Relationships window, or by dragging a field from the Field List pane.
Access uses table relationships to decide how to join tables when you need to use them in a database object.
Guide to table relationships - Access
There are several reasons why you should create table relationships before you create other database objects, such as forms, queries and reports. Table relationships inform your query designs To work with records from more than one table, you often must create a query that joins the tables.
The query works by matching the values in the primary key field of the first table with a foreign key field in the second table.
For example, to return rows that list all of the orders for each customer, you construct a query that joins the Customers table with the Orders table based on the Customer ID field. In the Relationships window, you can manually specify the fields to join. But, if you already have a relationship defined between the tables, Access supplies the default join, based on the existing table relationship.
In addition, if you use one of the query wizards, Access uses the information it gathers from the table relationships you have already defined to present you with informed choices and to prepopulate property settings with appropriate default values.
Table relationships inform your form and report designs When you design a form or report, Access uses the information it gathers from the table relationships you have already defined to present you with informed choices and to prepopulate property settings with appropriate default values. Table relationships are the foundation upon which you can enforce referential integrity to help prevent orphan records in your database.Creating a Relationship between two Tables in an Access Database
When you design a database, you divide your information into tables, each of which has a primary key. You then add foreign keys to related tables that reference those primary keys. These foreign key-primary key pairings form the basis for table relationships and multi-table queries. Referential integrity, which is dependent on table relationships, helps ensure that references stay synchronized. Top of Page Understanding referential integrity When you design a database, you divide your database information into many subject-based tables to minimize data redundancy.
You then give Access a way to bring the data back together by placing common fields into related tables. For example, to represent a one-to-many relationship you take the primary key from the "one" table and add it as an additional field to the "many" table.
To bring the data back together, Access takes the value in the "many" table and looks up the corresponding value in the "one" table. In this way the values in the "many" table reference the corresponding values in the "one" table. Suppose you have a one-to-many relationship between Shippers and Orders and you want to delete a Shipper.
If the shipper you want to delete has orders in the Orders table, those orders will become "orphans" when you delete the Shipper record. The orders will still contain a shipper ID, but the ID will no longer be valid, because the record that it references no longer exists.
The purpose of referential integrity is to prevent orphans and keep references in sync so that this hypothetical situation never occurs. You enforce referential integrity by enabling it for a table relationship see Enforce referential integrity for step-by-step instructions.
Once enforced, Access rejects any operation that violates referential integrity for that table relationship. This means Access will reject both updates that change the target of a reference, and deletions that remove the target of a reference. For such cases, what you really need is for Access to automatically update all the effected rows as part of a single operation. That way, Access ensures that the update is completed in full so that your database is not left in an inconsistent state, with some rows updated and some not.
When you enforce referential integrity and choose the Cascade Update Related Fields option, and you then update a primary key, Access automatically updates all fields that reference the primary key. When you enforce referential integrity and choose the Cascade Delete Related Records option, and you then delete a record on the primary key side of the relationship, Access automatically deletes all records that reference the primary key.
The Relationships window opens and displays any existing relationships. If no table relationships have been defined and you are opening the Relationships window for the first time, Access prompts you to add a table or query to the window. Open the Relationships window Click File, and then click Open. Select and open the database. On the Database Tools tab, in the Relationships group, click Relationships. If the database contains relationships, the Relationships window appears.
If the database does not contain any relationships and you are opening the Relationships window for the first time, the Show Table dialog box appears.
Click Close to close the dialog box. On the Design tab, in the Relationships group, click All Relationships. Open the table where you want to create a new lookup field by double-clicking it in the navigation. In the above example, click the Employees table. Click in the Field Name column just below the last field in the table and type a name for your new lookup field. In the example, type Region as the field name.
- In this article
- In this topic
In the Data Type column, click the arrow and select Lookup. The Lookup Wizard starts. On the first page of the Lookup Wizard, select I want the lookup field to get values from another table or query. More options appear in the dialog box. Select the name of the table or query that should provide the values for your lookup. In the example, select Table: After you select the table, use the Which value do you want to display in your lookup list to select the field that you want to use as a display value for your lookup field.
By default, Access selects the first text field it can find in the selected table. In the example, you would leave the selected field, Title, as the display value. Use the Do you want to sort the items in your lookup list to set the sorting, if you want.