Defining Relationships Between Tables
A well-designed Access database typically contains many interrelated tables, with each table containing a specific, narrowly defined set of data, without any duplicate information. This type of design is crucial to maintaining referential integrity—when you change a name, address, or other piece of data in one record, your change automatically appears in all related tables.
Imagine a database application that tracks customer purchases based on invoices. A proper design stores this information in four separate tables, as shown in Figure 34.2:
Figure 34.2. Note that each of the relationships between these four tables is tied to the primary key in one of the tables, shown in bold in the field list.
The Customers table contains one record for each of your customers, with their name, address, and phone number, plus a CustomerID field that uniquely identifies that customer.
The Products table contains one record for each unique product you sell, containing the name and description of the product, its wholesale and retail prices, and a unique ProductID field that identifies that product.
The Invoices table contains one record for each invoice you create, with an InvoiceNumber field that automatically creates a new, unique number when you start a new invoice, plus fields for the date and the name of the salesperson who filled in the invoice; each record also contains a CustomerID field that enables you to look up information from the matching record in the Customers table.
The Orders table contains one record for each item on each invoice; fields include InvoiceNumber, ProductID, and QuantitySold.
By storing information in multiple related tables, each of which has a specific purpose, you can extract results and produce reports that combine data in a wide variety of ways. For instance, by starting with the Invoices table, you can print out an invoice that includes the customer's name and address (drawn from the Customers table) and all the details for each product in that sale (drawn from the Products and Orders tables). If you start with the Customers table, you can prepare a monthly report that shows details and a grand total for each customer's orders that month. Or, by starting with the Products table, you can generate reports for each product showing sales by month and by customer, which might help you target advertising and promotions more effectively.
Before you can work with multiple tables in a database, you have to define a relationship between the tables. A relationship defines the fields that two tables have in common, so Access can combine information from the two tables into a logical result. In general, establishing a relationship between two tables requires that each table have a field in common with the other. Usually, the two common fields include the primary key for one table; the corresponding field in the second table is called the foreign key.
Tip from 
Although it's common for the fields that define a relationship to have the same name, it certainly isn't required. All that's necessary is that the two fields contain the same type of data. Thus, you could define a relationship between two tables by using the PostalCode field from one table and the ZipCode field from another table. Wherever the values match, Access combines the values in a query.
The most common type of relationship between tables is a one-to-many relationship, in which each record in a primary table can correspond to many records in a related table. Each record in the Suppliers table (the primary table) corresponds to one or more records in the Products table. Conversely, each product record must correspond to exactly one supplier record. No product can be recorded without a supplier. One-to-many relationships are common in everyday life: In a classroom, one teacher has many students; in a business, one customer has many orders and each invoice has many items.
Two other types of relationships are less common, but still occasionally useful:
As the name implies, one-to-one relationships store information in which a single record in one table corresponds to one and only one record in the second table.
Many-to-manyrelationships actually consist of multiple one-to-many relationships, with an intermediate table (sometimes called a junction table) pulling the results together. At a university, for example, each class consists of one teacher and many students, and each student's schedule includes multiple classes. By using a third table, you can produce a query or report showing the many-to-many relationship between a group of students and a group of teachers.
Tip from 
One-to-one relationships are useful when data security is an issue. In a business, for example, you might have an employee table that contains contact information such as e-mail addresses, phone numbers, department names, and locations. Another table contains information about salaries, performance reviews, and other sensitive information. Although only one record exists for each employee in each table, it would be foolish to combine the two tables and make the sensitive information visible to everyone. In this case, you set restrictions on user access to the second table, and then use a one-to-one relationship between the tables to create salary and benefits reports for authorized users.
Carefully defined table relationships are among the most important structural elements of a database. The Relationships window provides a clear graphic representation of all existing tables and queries for a database and enables you to define and edit the relationships between them. In the Relationships window, you can establish logical links between any combination of these objects.
If some of the relationships you define here don't appear in query windows, see
"Juggling Multiple Relationships"
in the
"Troubleshooting"
section at the end of this chapter.
Before attempting to edit or create relationships, first close any open tables. To open the Relationships window, choose Tools, Relationships. If you have not yet defined any relationships between tables in the current database, the Show Table dialog box appears automatically over the top of the Relationships window. If it doesn't appear, choose Relationships, Show Table. The Show Table dialog box displays a list of existing table objects in your database.
Defining a One-to-Many Relationship
To define a one-to-many relationship, you must first make sure the two tables are visible in the Relationships window. Next, drag the related field from one table and drop it onto the other. Finally, use the Edit Relationships dialog box to define the properties of the relationship itself. Follow these steps:
If the two tables are not visible, use the Show Table dialog box to select the first table—Suppliers, for instance—and click Add. A field list for the Suppliers table appears inside the Relationships window. Repeat this step until all tables are visible in the Relationships window. Then click the Close button.
Make sure the fields that define the relationship are visible in both field lists. Click the field in the first table and drag it on top of the matching field in the second table. When you release the mouse button, the Edit Relationships dialog box appears on the desktop, as shown in Figure 34.3. The Edit Relationships dialog box identifies the matching fields that link two tables. It also tells you the type of relationship that is being created.
Figure 34.3.
Examine the information displayed in the Edit Relationships dialog box to confirm that the definition is correct: In this example, Access is about to create a relationship between the Suppliers and Products tables, based on the SupplierID field in each table. Access has determined that this is a one-to-many relationship, as you can see in the Relationship Type box.
If the Relationship Type box identifies the relationship as Indeterminate, you'll need to make a few small repairs; see
"Firming Up a Relationship"
in the
"Troubleshooting"
section for some suggestions.If you want to ensure consistency in your data, check the Enforce Referential Integrity box.
Click Create to define the relationship. Access adds a bold line connecting the fields in each table. The symbols displayed above the line indicate the direction of the one-to-many definition.
Working with One-to-Many Relationships
After creating a one-to-many relationship, you can see one important effect of the new relationship when you open the primary table (the table on the "one" side of the relationship) in Datasheet view. Access automatically creates a subdatasheet in this table, enabling you to view corresponding records from the Products table. The only visible sign of the subdatasheet, at least initially, is a column of plus signs (known as expand indicator icons) at the left side of the Suppliers table. Click any one of these icons to see details from the related table (the "many" side of the relationship) for the selected record. As you can see in the example in Figure 34.4, Access displays all the Products records for the selected record in the Suppliers table.
Figure 34.4. When you open a primary table that is part of a one-to-many relationship, you can expand or collapse the subdatasheet to control the display of data from the related table.
