Office 365 ProPlus is being renamed to jonathanlewisforcongress.com 365 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 jonathanlewisforcongress.com Access database (.mdb or .accdb).
This article describes how to define relationships in a jonathanlewisforcongress.com Access database. The article includes the following topics:
What are table relationships?Kinds of table relationshipsOne-to-many relationshipsMany-to-many relationshipsOne-to-one relationshipsHow to define relationships between tablesHow to define a one-to-many or one-to-one relationshipHow to define a many-to-many relationshipReferential integrityCascading updates and deletesJoin types
What are table relationships?
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. There is also information that you might want to store about the publisher, such as the publisher”s telephone number, address, and ZIP Code/Postal Code. 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.” You would then put a pointer in the “Titles” table that references an entry in the “Publishers” table.
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.
Logical relationships in a database enable you to efficiently query data and create reports.
Kinds of table relationships
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.” For example, if you want to track sales of each book title, you create a relationship between the primary key column (let”s call it title_ID) in the “Titles” table and a column in the “Sales” table that is named title_ID. The title_ID column in the “Sales” table is 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. The primary key of the “TitleAuthors” table is the combination of the au_ID column (the “Authors” table”s primary key) and the title_ID column (the “Titles” table”s primary key).
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:
Divide a table with many columns.Isolate part of a table for security reasons.Store data that is short-lived and could be easily deleted by deleting the table.Store information that applies only to a subset of the main table.
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.
How to define relationships between tables
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. For example, you can match an AutoNumber field and a Number field if theFieldSizeproperty of both fields isLong Integer. Even when both matching fields are Number fields, they must have the sameFieldSizeproperty setting.
How to define a one-to-many or one-to-one relationship
To create a one-to-many or a one-to-one relationship, follow these steps:
Close all tables. You cannot create or change relationships between open tables.
In Access 2002 or Access 2003, follow these steps:
Press F11 to switch to the Database window.On the Tools menu, click Relationships.
In Access 2007, Access 2010, or Access 2013, click Relationships in the Show/Hide group on the Database Tools tab.
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.)
Click Create to create the relationship.
Repeat steps 4 through 7 for each pair of tables that you want to relate.
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.