In the Field List pane, when you click the plus sign (+) next to a table name, you see a list of all the fields available in that table. To add a field to your table, drag the field that you want from the Field List pane to the table in Datasheet view.
Add a field and create a relationship from the Field List pane
With the table open in Datasheet view, press ALT+F8. The Field List pane appears.
Under Fields available in other tables, click the plus sign (+) next to a table name to display the list of fields in that table.
Drag the field that you want from the Field List pane to the table that is open in Datasheet view.
When the insertion line appears, drop the field into position.
The Lookup Wizard starts.
Follow the instructions to complete the Lookup Wizard.
The field appears in the table in Datasheet view.
When you drag a field from an 'other' (unrelated) table and then complete the Lookup Wizard, a new one-to-many relationship is automatically created between the table in the Field List and the table to which you dragged the field. This relationship, created by Access, does not enforce referential integrity by default. To enforce referential integrity, you must edit the relationship. See the section Edit a relationship for more information.
Edit a relationship
You change a relationship by selecting it in the Relationships window and then editing it.
Carefully position the cursor so that it points to the relationship line, and then click the line to select it.
The relationship line appears thicker when it is selected.
With the relationship line selected, double-click it.
Today I have to face a weird behavior in my laptop since a few of days ago. It only happens that when the computer goes to sleep, that is, stand-by, either by my request or due to lack of battery, the screen goes blank but seemed to never finish the sleep process.I say “seemed” because the last times this happened, I did not have the patience to wait and long-pressed the power button to force a shutdown.
Bsod memory dump windows 7.
–or–
On the Design tab, in the Tools group, click Edit Relationships.
The Edit Relationships dialog box appears.
Open the Edit Relationships dialog box
On the Database Tools tab, in the Relationships group, click Relationships.
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.
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. The relationship line appears thicker when it is selected.
Double-click the relationship line.
-or-
On the Design tab, in the Tools group, click Edit Relationships.
The Edit Relationships dialog box appears.
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. You create a query that joins the Customers table and the Orders table on the Customer ID field. 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 join), the query returns only the Customer rows and the Order rows where the common fields (also called the joined fields) are equal.
However, suppose you want to include all Customers — even those who have not yet placed any orders. To accomplish this, you have to change the join type from an inner join to what is known as a left outer join. A left outer join returns all of the rows in the table on the left side of the relationship and only those that match in the table on the right. A right outer join returns all of the rows on the right and only those that match on the left.
Note: In this case, 'left' and 'right' refer to the position of the tables in the Edit Relationships dialog box, not the Relationships window.
You should think about the result you will most often want from a query that joins the tables in this relationship, and then set the join type accordingly.
Set the join type
In the Edit Relationships dialog box, click Join Type.
The Join Properties dialog box appears.
Click your choice, and then click OK.
The following table (using the Customers and Orders tables) shows the three choices that are displayed in the Join Properties dialog box, the type of join they use, and whether all rows or matching rows are returned for each table.
Choice | Relational join | Left table | Right table |
|---|
1. Only include rows where the joined fields from both tables are equal. | Inner join | Matching rows | Matching rows |
2. Include ALL records from 'Customers' and only those records from 'Orders' where the joined fields are equal. | Left outer join | All rows | Matching rows |
3. Include ALL records from 'Orders' and only those records from 'Customers' where the joined fields are equal. | Right outer join | Matching rows | All rows |
When you choose option 2 or option 3, an arrow is shown on the relationship line. This arrow points to the side of the relationship that shows only matching rows.
Make changes in the Join Properties dialog box
On the Database Tools tab, in the Relationships group, click Relationships.
Note: 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.
All tables that have 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. The relationship line appears thicker when it is selected.
Double-click the relationship line. The Edit Relationships dialog box appears.
Click Join Type.
In the Join Properties dialog box, click an option, and then click OK.
Make any additional changes to the relationship, and then click OK.
Enforce referential integrity
The purpose of referential integrity is to prevent orphan records – records that reference other records that no longer exist. You enforce referential integrity by enabling it for a table relationship. Once enforced, Access rejects any operation that would violate referential integrity for that table relationship. This means that Access will reject both updates that change the target of a reference and deletions that remove the target of a reference. To have Access propagate referential updates and deletions so that all related rows are changed accordingly, see the Set the cascade options section.
Turn referential integrity on or off
On the Database Tools tab, in the Relationships group, click Relationships.
On the Design tab, in the Relationships group, click All Relationships.
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. The relationship line appears thicker when it is selected.
Double-click the relationship line. The Edit Relationships dialog box appears.
Select or clear Enforce Referential Integrity.
Make any additional changes to the relationship, and then click OK.
If you enforce referential integrity, the following rules apply:
You cannot enter a value in the foreign key field of a related table if that value doesn't exist in the primary key field of the primary table — doing so creates orphan records.
You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the Employees table if there are orders assigned to that employee in the Orders table. You can, however, choose to delete a primary record and all related records in one operation by selecting the Cascade Delete Related Records check box.
You cannot change a primary key value in the primary table if doing so would create orphan records. For example, you cannot change an order number in the Orders table if there are line items assigned to that order in the Order Details table. You can, however, choose to update a primary record and all related records in one operation by selecting the Cascade Update Related Fields check box.
Notes: If you have difficulty enabling referential integrity, note that the following conditions are required to enforce referential integrity:
The common field from the primary table must be a primary key or have a unique index.
The common fields must have the same data type. The one exception is that an AutoNumber field can be related to a Number field that has a FieldSize property setting of Long Integer.
Both tables exist in the same Access database. Referential integrity cannot be enforced on linked tables. However, if the source tables are in Access format, you can open the database in which they are stored and enable referential integrity in that database.
Set the cascade options
You might encounter a situation where you have a valid need to change the value on the 'one' side of a relationship. In such a case, you need Access to automatically update all of the affected rows as part of a single operation. That way the update is completed in full so that your database is not left in an inconsistent state — with some rows updated and some not. Access helps you avoid this problem by supporting the Cascade Update Related Fields option. 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.
You might also need to delete a row and all related records — for instance, a Shipper record and all related orders for that shipper. For this reason, Access supports the Cascade Delete Related Records option. When you enforce referential integrity and select the Cascade Delete Related Records check box, Access automatically deletes all records that reference the primary key when you delete the record that contains the primary key.
Turn cascade update and/or cascade delete on or off
On the Database Tools tab, in the Relationships group, click Relationships.
On the Design tab, in the Relationships group, click All Relationships.
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. The relationship line appears thicker when it is selected.
Double-click the relationship line.
The Edit Relationships dialog box appears.
Select the Enforce Referential Integrity check box.
Select either the Cascade Update Related Fields or the Cascade Delete Related Records check box, or select both.
Make any additional changes to the relationship, and then click OK.
Note: If the primary key is an AutoNumber field, selecting the Cascade Update Related Fields check box will have no effect, because you cannot change the value in an AutoNumber field.
Delete a table relationship
Important: When you remove a relationship, you also remove referential integrity support for that relationship, if it is enabled. As a result, Access will no longer automatically prevent the creation of orphan records on the 'many' side of a relationship.
To remove a table relationship, you must delete the relationship line in the Relationships window. Position the cursor so that it points to the relationship line, and then click the line. The relationship line appears thicker when it is selected. With the relationship line selected, press DELETE.
On the Database Tools tab, in the Relationships group, click Relationships.
On the Design tab, in the Relationships group, click All Relationships.
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 delete. The relationship line appears thicker when it is selected.
Press the DELETE key.
Access might display the message Are you sure you want to permanently delete the selected relationship from your database?. If this confirmation message appears, click Yes.
Note: If either of the tables employed in the table relationship are in use — perhaps by another person or process, or in an open database object, such as a form — you will not be able to delete the relationship. You must close any open objects that employ these tables before you try to remove the relationship.
Create, edit, or delete a relationship in an Access web app
There are important differences when working with relationships in an Access web app.
Create a relationship
The Relationships window isn't available in an Access web app. Instead of creating a relationship in an Access web app, you create a lookup field that gets values from a related field in another table. For example, let’s say you have an Employees table and you want to add a lookup to a Regions table so you can show which region each employee works in.
Note: The field that your lookup will use as the source for values must already exist before you create your lookup field.
Here’s how you create a lookup field in an Access web app:
Open the table where you want to create a new lookup field by double-clicking it in the navigation. (Hint: you may need to click Home > Navigation Pane to see the available tables.)
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 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: Regions.
(Names of the tables in the image correspond to the example we’re using.)
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.
Under What should happen when a record from the “Regions” table is deleted, set the type of relationship you want between the two tables and whether you want to enforce referential integrity. (The name of the table in this question varies depending on which table you selected in step 5.)
The Lookup Wizard defaults to Prevent delete if there are corresponding records in the “Employees” table, because that’s the safest option in most cases. In the example, this option means you can’t delete a value from the Regions table if that region is being used in records in the Employees table. So, if employee records are using a region, such as “West” and you try to delete “West” from the Regions table, Access will prevent you from deleting it. In this case, you need to reset all employee records using that value to something else, before you can delete “West” from the Regions table. The last option could work in this example, because that would allow you to delete “West” from the Regions table. The region value would be automatically removed from Employee records that were set to “West,” leaving the value blank. Choosing the second option would delete all employee records from the Employees table that have the region set to “West.” That’s called a cascading delete and would delete much more data than you want in the example. Be careful when choosing that option.
Edit a relationship
The Relationships window isn't available in an Access web app. You use a field in one table as the source (lookup field) for values in the related field in another table.
Open the table that contains the lookup you want to change by clicking Edit Table.
Select the field that gets its values from another table.
On the Design tab, under Table Tools, click Modify Lookups.
Follow the steps in the wizard to make the changes that you want. You can change the following:
The field that is the source of values
The sort order of the values
The width of the field, and whether to hide the key column
The label for the lookup field
Whether data integrity is enabled
If data integrity is enabled, whether deletes are cascaded or restricted
Delete a relationship
How To Create Relationships In Access
The Relationships window isn't available in an Access web app. You use a field in one table as the source (lookup field) for values in the related field in another table. In order to delete a relationship between two tables in an Access web app, you need to delete the lookup field and its associated data.
Open the table that contains the lookup field you want to delete by clicking Edit Table.
Select the lookup field that gets its values from another table in the web app.
On the Design tab, under Table Tools, click Delete Fields.
Access displays the message Do you want to permanently delete the selected fields(s) and all the data in the field(s)? Click Yes to confirm deleting the lookup field and its data.
Note: You might also be prompted by Access to delete one or more indexes when deleting the lookup field. If this confirmation message appears, click Yes.
Click Save to save your table design changes.
Create or modify a relationship in an Access 2010 web database
To create a relationship in an Access 2010 web database, you use the Lookup Wizard. The Relationships window is not available in a web database. You use a field in one table as the source for values in the related field in another table.
Note: The field that you use as the source for values must already exist before you can use the Lookup Wizard.
Create a relationship in an Access 2010 web database by using the Lookup Wizard
How To Make A Foreign Key In Access
Open the table that should get its values from the other table.
To the right of the last field, click Click to Add, and then click Lookup & Relationship.
Note: You might need to scroll horizontally to see Click to Add.
On the first screen of the Lookup Wizard, select I want the lookup field to get the values from another table, and then click Next.
Select the table that has the source values, and then click Next.
In the Available Fields box, double-click the field that has the source values, and then click Next.
Optionally, specify a sort order for the lookup field. Click Next.
Optionally, adjust the width of the lookup field – a good idea if the values are long. Click Next.
Enter a name for the new field. If you want to make sure that the values in the two tables always match, select the Enable Data Integrity check box, and then click one of the following:
Cascade Delete – this option means that if you delete a record from one table, corresponding records in the other table are also deleted.
Restrict Delete – this option means that if you attempt to delete a record from one table but there is a corresponding record in the other table, the delete operation is not allowed.
Note: Do not select Allow Multiple Values in the Lookup Wizard when you are using the wizard to create a relationship.
Modify a relationship in an Access 2010 web database
Open the table that gets its values from another table.
Select the field that gets its values from another table.
On the Fields tab, in the Properties group, click Modify Lookups.
Follow the steps in the wizard to make the changes that you want. You can change the following:
The field that is the source of values
The sort order of the values
The width of the field, and whether to hide the key column
The label for the lookup field
Whether data integrity is enabled
If data integrity is enabled, whether deletes are cascaded or restricted
A primary key is a field or set of fields with values that are unique throughout a table. Values of the key can be used to refer to entire records, because each record has a different value for the key. Each table can only have one primary key. Access can automatically create a primary key field for you when you create a table, or you can specify the fields that you want to use as the primary key. This article explains how and why to use primary keys.
To set a table’s primary key, open the table in Design view. Select the field (or fields) that you want to use, and then on the ribbon, click Primary Key.
Note: This article is intended for use only with Access desktop databases. Access automatically manages primary keys for new tables in Access web apps and web databases. Although it is possible to override these automated primary keys, we don’t recommend that you do.
In this article
Overview of primary keys in Access
Access uses primary key fields to quickly associate data from multiple tables and combine that data in a meaningful way. You can include the primary key fields in other tables to refer back to the table that is the source of the primary key. In those other tables, the fields are called foreign keys. For example, a Customer ID field in the Customers table might also appear in the Orders table. In the Customers table, it is the primary key. In the Orders table it is called a foreign key. A foreign key, simply stated, is another table's primary key. For more information, see Database design basics.
1. Primary key
2. Foreign key
If you are moving existing data into a database, you may already have a field that you can use as the primary key. Often, a unique identification number, such as an ID number or a serial number or code, serves as a primary key in a table. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key.
Access automatically creates an index for the primary key, which helps speed up queries and other operations. Access also ensures that every record has a value in the primary key field, and that it is always unique.
When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of 'ID' and the AutoNumber data type.
What makes a good primary key?
A good candidate for a primary key has several characteristics:
It uniquely identifies each row
It is never empty or null — it always contains a value
The values it contains rarely (ideally, never) change
If you can’t identify a good key, create an AutoNumber field to use as the key. An AutoNumber field automatically generates a value for itself when each record is first saved. Therefore, an AutoNumber field meets all three characteristics of a good primary key. For more information on adding an AutoNumber field, see the article Add an AutoNumber field as a primary key.
An AutoNumber field makes a good primary key.
Examples of poor primary keys
Any field that is missing one or more of the characteristics of a good candidate key is a poor choice for a primary key. Here are a few examples of fields that would make poor primary keys for a Contacts table, along with reasons why they would be poor choices.
Poor primary key | Reason |
|---|
Personal name | Might not be reliably unique, and may change |
Phone number | Likely to change. |
E-mail address | Likely to change. |
Zip code | More than one person may share a ZIP code |
Combinations of facts and numbers | The fact portion might change, creating a maintenance burden. Could lead to confusion if the fact portion is repeated as a separate field. For example, combining the city and an incremented number (e.g., NEWYORK0579) would be a poor choice if the city is also stored as a field. |
Social Security Numbers | Private information and not allowed in government departments and some organizations. Some people don’t have a SSN An individual may have more than one in a lifetime
|
Composite keys: using multiple fields in combination as a primary key
In some cases, you want to use two or more fields in a table as the primary key. For example, an Order Details table that stores line items for orders might use two fields in its primary key: Order ID and Product ID. A key that has more than one field is called a composite key.
Set the primary key using fields you already have in Access
For a primary key to work well, the field must uniquely identify each row, never contain an empty or null value, and rarely (ideally, never) change. To set the primary key:
Open the database that you want to modify.
In the Navigation Pane, right click the table in which you want to set the primary key and, on the shortcut menu, click Design View.
Tip: If you don't see the Navigation Pane, press F11 to display it.
Select the field or fields that you want to use as the primary key.
To select one field, click the row selector for the field you want.
To select more than one field to create a composite key, hold down CTRL and then click the row selector for each field.
On the Design tab, in the Tools group, click Primary Key.
A key indicator is added to the left of the field or fields that you specify as the primary key.
Remove a primary key in Access
When you remove the primary key, the field or fields that previously served as primary key will no longer provide the primary means of identifying a record.
Removing the primary key doesn’t delete the fields from your table, but it does remove the index that was created for the primary key.
Before you can remove a primary key, you must make sure that it doesn't participate in any table relationships. If you try to remove a primary key that is part of one or more relationships, Access warns you that you must delete the relationships first.
To delete a table relationship, complete the following steps:
If the tables that participate in the table relationship are open, close them. You cannot delete a table relationship between open tables.
On the Database Tools tab, in the Relationships group, click Relationships.
If the tables that participate in the table relationship are not visible, on the Design tab, in the Relationships group, click Show Table.
Select the tables to add in the Show Table dialog box, and then click Add, and click Close.
Click the table relationship line for the table relationship that you want to delete (the line becomes bold when it is selected), and then press the DELETE key.
On the Design tab, in the Relationships group, click Close.
After you delete the relationships, in the Navigation Pane, right click the table from which you want to remove the primary key and then click Design View.
Tip: If you don't see the Navigation Pane, press F11 to display it.
Click the row selector for the current primary key.
If the primary key consists of a single field, click the row selector for that field.
If the primary key consists of multiple fields, click the row selector for any field in the primary key.
On the Design tab, in the Tools group, click Primary Key.
The key indicator is removed from the field or fields that you previously specified as the primary key.
Note: When you save a new table without setting a primary key, Access prompts you to create one. If you choose Yes, Access creates an ID field that uses the AutoNumber data type to provide a unique value for each record. If your table already includes an AutoNumber field, Access uses that field as the primary key.
Change the primary key in Access
If you decide to change a table's primary key, you can do so by following these steps:
Remove the existing primary key using the instructions in the section Remove the primary key.
Set the primary key using the instructions in the section Set the primary key.
Additional information
For more information about database design and choosing an appropriate primary key, see the articles:
One of the goals of good database design is to remove data redundancy (duplicate data). To achieve that goal, you divide your data into many subject-based tables so that each fact is represented only once. You then provide Access with a way to bring the divided information back together — you do this by placing common fields in tables that are related. To do this step correctly, though, you have to understand the relationships between your tables, and then specify these relationships in your database.
In this article
Introduction
After you have created a table for each subject in your database, you have to give Access a way to bring that information back together again when needed. You do this by placing common fields in tables that are related, and by defining relationships between your tables. You can then create queries, forms, and reports that display information from several tables at once. For example, the form shown here includes information drawn from several tables:
1. Information in this form comes from the Customers table..
2. ..the Orders table..
3. ..the Products table..
4. ..and the Order Details table.
The customer name in the Bill To box is retrieved from the Customers table, the Order ID and the Order Date values come from the Orders table, the Product name comes from the Products table, and the Unit Price and Quantity values come from the Order Details table. These tables are linked to each other in a variety of ways to bring information from each into the form.
In the preceding example, the fields in the tables must be coordinated so that they show information about the same order. This coordination is accomplished by using table relationships. A table relationship works by matching data in key fields — often a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. For example, employees can be associated with orders for which they are responsible by creating a table relationship between the EmployeeID fields in the Employees and the Orders tables.
1. EmployeeID appears in both tables — as a primary key ..
2. .. and as a foreign key.
Types of table relationships
There are three types of table relationships in Access.
A one-to-many relationship
Let’s use an order tracking database that includes a Customers table and an Orders table as an example. A customer can place any number of orders. It follows that for any customer represented in the Customers table, there might be many orders represented in the Orders table. The relationship between the Customers table and the Orders table is a one-to-many relationship.
To represent a one-to-many relationship in your database design, take the primary key on the 'one' side of the relationship and add it as an additional field or fields to the table on the 'many' side of the relationship. In this case, for example, you add a new field — the ID field from the Customers table — to the Orders table and name it Customer ID. Access can then use the Customer ID number in the Orders table to locate the correct customer for each order.
A many-to-many relationship
Now let’s look at the relationship between a Products table and an Orders table. 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.
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. 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. An orphan record is a record with a reference to another record that does not exist — for example, an order record that references a customer record that does not exist.
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. It’s important that these foreign key-primary key references stay synchronized. Referential integrity, which is dependent on table relationships, helps ensure that references stay synchronized.
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. It’s possible you might have a perfectly valid need to change the primary key for a shipper that has orders in the Orders table. 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. For this reason Access supports the Cascade Update Related Fields option. 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.
It's also possible you might have a valid need to delete a row and all related records — for example, a Shipper record and all related orders for that shipper. For this reason, Access supports the Cascade Delete Related Records option. 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.
View table relationships
To view your table relationships, click Relationships on the Database Tools tab. 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.
This displays all of the defined relationships in your database. 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 the Show Hidden Objects check box is selected in the Navigation Options dialog box.
A table relationship is represented by a relationship line drawn between tables in the Relationships window. A relationship that does not enforce referential integrity appears as a thin line between the common fields supporting the relationship. When you select the relationship by clicking its line, the line thickens to indicate it is selected. If you enforce referential integrity for this relationship, the line appears thicker at each end. In addition, the number 1 appears over the thick portion of the line on one side of the relationship, and the infinity symbol (∞) appears over the thick portion of the line on the other side.
When the Relationships window is active, you can select from the following commands on the ribbon:
On the Design tab, in the Tools group:
Edit Relationships Opens the Edit Relationships dialog box. When you select a relationship line, you can click Edit Relationships to change the table relationship. You can also double-click the relationship line.
Clear Layout Removes all tables and relationships from display in the Relationships window. Note that this command only hides the tables and relationships — it does not delete them.
Relationships Report Creates a report that displays the tables and relationships in your database. The report shows only the tables and relationships that are not hidden in the Relationships window.
On the Design tab, in the Relationships group:
Show Table Opens the Show Table dialog box so that you can select tables and queries for viewing in the Relationships window.
Hide Table Hides the selected table in the Relationships window.
Direct Relationships Telcordia
issue 4. Displays all relationships and related tables for the selected table in the Relationships window, if they are not already displayed.
All Relationships Displays all of the relationships and related tables in your database in the Relationships window. 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.
Close Closes the Relationships window. If you made any changes to the layout of the Relationships window, you are asked whether to save those changes.
