Creating Relationships Between Tables
In an Access database, you create a table relationship using one of the following methods: In the Relationships window, add the tables that you want to relate, and then drag the field to relate them from one table to the other table. Drag a field on to a table datasheet from the Field List pane. Sep 25, · This is video 2 in my tutorial series(!). The videos are being produced for my lovely students at the Grammar School Nicosia.
Reviewing One-To-Many Relationships. In the Database window, select Tables and double-click the Products table to open it. Note that each product has been assigned a specific category Beverages, Condiments, etc. Close the table. Next, open the Categories table. This table only has data about product categories.
Each category kn appear many times in the Products table but appears only once in the Categories table. Close the Categories table. Choose Tools from the main menu and select Relationships. Locate the Categories and Products field lists. Note that a one-to-many relationship has been established between the tables through the CategoryID field, which contains data common to both tables. Categories, the table on the relatiinships side of the relationship, has a 1 next to CategoryID; in the Products table, the table on the many side, CategoryID has the infinity sign?
Thus referential integrity has been enforced, which very briefly means you cannot remove a record from the Categories table if it has related records in the Products table.
Sometimes you will find it necessary to delete a relationship, relationshipps to change the structure of your database or to perform an operation that the relationship prohibits. Choose Creatf and click Yes to creatte. To recreate the relationship, click CategoryID in relationshups Categories table. The Edit Relationships dialog box opens. The grayed-out table names on top should be Categories and Products. Click Create. Note that, unlike the other relationships in the window, there are no symbols 1 and infinity at either end of the line between the two field lists.
Unlike the other lists, referential integrity has not been enforced. Right-click the line again and choose Edit Relationship. The symbols are now displayed. Even in a small database like Northwind, the Relationships window is sufficiently confusing in that it can be difficult to determine the relationships for a single table.
In a database with 40 tables, it can be creqte. You can view the relationships for a single how to create relationships in access 2003 alone. Choose Edit and select Clear Layout. Click Yes to confirm. The Relationships window is blank.
Choose Relationships and select Show Table. In the Show Table dialog how to design and build a sprinkler system, double-click Products to add it to the Relationships window. Click Close. Choose Relationships and select Show Direct. Ti of the tables that have relationships with Products are added to how to create relationships in access 2003 window and displayed.
If you have other field lists in the window and you want to show the direct relationships for only one table, select it and choose Relationships and Show Relationships. Note that, when there are other field lists, choosing this command will not remove any of them from the window.
At this point, the relationship lines in the window are like how to create relationships in access 2003 spaghetti. You can move the field lists around click the title bar and drag so the relationships can be clearly seen. Suppose you once t want to see all the relationships in the what is a buddy bench. Choose Relationships and select Show All.
Now your window is really a mess! How can you get back to that clean, easy-to-read display you began with? Close the Relationships window. Access asks you if you want to save the changes.
The question refers only to the layout of the window. Click No. Choose Tools from the main menu and click Relationships, and your display is back to that as seen in Figure 1.
Print The Relationships Window. You may want to print the Relationships window for, say, your database designer. Accfss File and Print Relationships. Access creates a report that can be printed like any avcess. Note, however, that if you save the report, it will not be updated for changes you make in the Relationships window. You have to create another report. Also, note that creating the report saves relationsyips layout. If you wish, choose File im Print to print the report.
Close the Relationships report without saving it. Close the Relationships window, close the database, and exit Access.
TO DEFINE TABLE RELATIONSHIPS:
Right-click the line between the Products and Categories tables (you must click directlyon the line or the menu won’t appear). Choose Delete and click Yes to confirm. To recreate the relationship, click CategoryID in the Categories table. Drag-and-drop CategoryID from the Categories table directly on CategoryID in the Products table. Jun 30, · FREE Course! Click: datmelove.com how to use the relationships window in Microsoft Access at datmelove.com Get the complete tuto. Access In Pictures. Database Basics; Working with Tables; Working with Forms; Working with Queries; Working with Reports; Create table relationships. What are "relationships?" Relationships are links that associate a field in one table with a field in another. An example is .
Office ProPlus is being renamed to Microsoft Apps for enterprise. For more information about this change, read this blog post. Novice: Requires knowledge of the user interface on single-user computers. This article applies only to a Microsoft Access database. This article describes how to define relationships in a Microsoft Access database. The article includes the following topics:.
In a relational database, relationships enable you to prevent redundant data. For example, if you are designing a database that will track information about books, you might have a table named "Titles" that stores information about each book, such as the book's title, date of publication, and publisher.
If you were to store all this information in the "Titles" table, the publisher's telephone number would be duplicated for each title that the publisher prints. A better solution is to store the publisher's information only one time, in a separate table that we will call "Publishers. To make sure that you data stays synchronized, you can enforce referential integrity between tables.
Referential integrity relationships help make sure that information in one table matches information in another. For example, each title in the "Titles" table must be associated with a specific publisher in the "Publishers" table. A title cannot be added to the database for a publisher that does not exist in the database. A relationship works by matching data in key columns, usually columns or fields that have the same name in both tables.
In most cases, the relationship connects the primary key, or the unique identifier column for each row, from one table to a field in another table. The column in the other table is known as the "foreign key. There are three kinds of relationships between tables.
The kind of relationship that is created depends on how the related columns are defined. A one-to-many relationship is the most common kind of relationship. In this kind of relationship, a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A.
For example, the "Publishers" and "Titles" tables have a one-to-many relationship. That is, each publisher produces many titles. But each title comes from only one publisher. A one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint. In the relationship window in Access, the primary key side of a one-to-many relationship is denoted by a number 1.
The foreign key side of a relationship is denoted by an infinity symbol. In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table that is called a junction table. The primary key of the junction table consists of the foreign keys from both table A and table B. For example, the "Authors" table and the "Titles" table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the "TitleAuthors" table.
In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints. This kind of relationship is not common, because most information that is related in this manner would be in one table. You might use a one-to-one relationship to take the following actions:. In Access, the primary key side of a one-to-one relationship is denoted by a key symbol.
The foreign key side is also denoted by a key symbol. When you create a relationship between tables, the related fields do not have to have the same names. However, related fields must have the same data type unless the primary key field is an AutoNumber field. You can match an AutoNumber field with a Number field only if theFieldSizeproperty of both of the matching fields is the same.
Even when both matching fields are Number fields, they must have the sameFieldSizeproperty setting. If you have not yet defined any relationships in your database, the Show Table dialog box is automatically displayed.
If you want to add the tables that you want to relate but the Show Table dialog box does not appear, click Show Table on the Relationships menu. Double-click the names of the tables that you want to relate, and then close the Show Table dialog box. To create a relationship between a table and itself, add that table two times. Drag the field that you want to relate from one table to the related field in the other table. To drag multiple fields, press Ctrl, click each field, and then drag them.
In most cases, you drag the primary key field this field is displayed in bold text from one table to a similar field this field frequently has the same name that is called the foreign key in the other table.
The Edit Relationships dialog box appears. Make sure that the field names that are displayed in the two columns are correct. You can change the names if it is necessary.
Set the relationship options if it is necessary. If you have to have information about a specific item in the Edit Relationships dialog box, click the question mark button, and then click the item. These options will be explained in detail later in this article. When you close the Edit Relationships dialog box, Access asks whether you want to save the layout.
Whether you save the layout or do not save the layout, the relationships that you create are saved in the database. You can create relationships not only in tables but also in queries. However, referential integrity is not enforced with queries. Create a third table. This is the junction table. In the junction table, add new fields that have the same definitions as the primary key fields from each table that you created in step 1. In the junction table, the primary key fields function as foreign keys.
You can add other fields to the junction table, just as you can to any other table. In the junction table, set the primary key to include the primary key fields from the other two tables.
Select the field or fields that you want to define as the primary key. To select one field, click the row selector for the desired field.
To select multiple fields, hold down the Ctrl key, and then click the row selector for each field. If you want the order of the fields in a multiple-field primary key to differ from the order of those fields in the table, click Indexes on the toolbar to display the Indexes dialog box, and then reorder the field names for the index named PrimaryKey.
Referential integrity is a system of rules that Access uses to make sure that relationships between records in related tables are valid, and that you do not accidentally delete or change related data.
You can set referential integrity when all the following conditions are true:. For relationships in which referential integrity is enforced, you can specify whether you want Access to automatically cascade update or cascade delete related records. If you set these options, delete and update operations that would usually be prevented by referential integrity rules are enabled.
When you delete records or change primary key values in a primary table, Access makes the necessary changes to related tables to preserve referential integrity. If you click to select the Cascade Update Related Fields check box when you define a relationship, any time that you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records.
For example, if you change a customer's ID in the "Customers" table, the CustomerID field in the "Orders" table is automatically updated for every one of that customer's orders so that the relationship is not broken.
Access cascades updates without displaying any message. If the primary key in the primary table is an AutoNumber field, selecting the Cascade Update Related Fields check box has no effect because you cannot change the value in an AutoNumber field. If you select the Cascade Delete Related Records check box when you define a relationship, any time that you delete records in the primary table, Access automatically deletes related records in the related table.
For example, if you delete a customer record from the "Customers" table, all the customer's orders are automatically deleted from the "Orders" table.
This includes records in the "Order Details" table that are related to the "Orders" records. When you delete records from a form or datasheet when the Cascade Delete Related Records check box selected, Access warns you that related records may also be deleted.
However, when you delete records by using a delete query, Access automatically deletes the records in related tables without displaying a warning. Option 1 defines an inner join. An inner join is a join in which records from two tables are combined in a query's results only if values in the joined fields meet a specified condition. In a query, the default join is an inner join that selects records only if values in the joined fields match.
Option 2 defines a left outer join. A left outer join is a join in which all the records from the left side of the LEFT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the right side. Option 3 defines a right outer join. A right outer join is a join in which all the records from the right side of the RIGHT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the left side.
Skip to main content. Contents Exit focus mode. Note Novice: Requires knowledge of the user interface on single-user computers. Note You can create relationships not only in tables but also in queries. Note If you want the order of the fields in a multiple-field primary key to differ from the order of those fields in the table, click Indexes on the toolbar to display the Indexes dialog box, and then reorder the field names for the index named PrimaryKey.
Note If the primary key in the primary table is an AutoNumber field, selecting the Cascade Update Related Fields check box has no effect because you cannot change the value in an AutoNumber field. Is this page helpful? Yes No. Any additional feedback?