Linking Tables

As you create tables and work with your data, you will begin to see ways that you can do even more with it. By linking tables, you can extend the functionality of your data by joining tables, and looking at the data in different ways. This section gives you a glimpse of relationships among tables, so that you can explore more thoroughly on your own.

Understanding Relationships

Access allows three kinds of relationships between tables:

• One-to-one relationship This relationship is rarely used, because it typically can be handled within a single data table. However, an example of a one-to-one relationship might be when you want to link an employee s attendance record with a bonus schedule. The Employee ID field is the field unique to both tables, on which they would be linked.

• One-to-many relationship This is the most common data relationship, in which one item in one table can be linked to many items in the second table. An example of a one-to-many relationship would be a link between a Sales Staff table and the Orders database. One salesperson can submit many different orders, but each order can have only one salesperson.

• Many-to-many relationship This type of relationship is for complex data relationships, in which each item can be linked to multiple items. Access uses a third table, called a junction table , to store the primary and foreign keys (the fields on which the relationships are linked). An example of a many-to-many relationship is a book database, in which each book can be sold to multiple customers and be purchased from multiple vendors .

Creating Relationships

To create a link between tables, perform these steps:

1. Close any tables you have open , so that only the Database window is displayed.

2. Click Relationships on the Standard toolbar. If there are no relationships established in your database, the Show Table dialog box appears (as shown in Figure 17-6 on the following page).

   
   Figure 17-6: Use Show Table to choose the tables you want to use to set up relationships.

   Note  

   If you used the Access Database Wizard to create your database, links may already be established between your tables. In this case, the Show Table dialog box does not appear; instead, the Relationship window opens, showing the tables and the links established between them.

3. In the Show Table dialog box, click the Table, Queries, or Both tabs, depending on the items you want to include in your relationships. Select the tables you want to use, by clicking each one and clicking Add. When you re finished adding tables and queries, click Close, and the Relationships window is displayed (as shown in Figure 17-7).

   
   Figure 17-7: The Relationships window shows the tables among which you ll establish relationships.

4. To create a relationship between tables, drag the field from one table to the corresponding field in the second table. In the Edit Relationships dialog box, click Create. The link is made between the tables.

5. Close the Relationships window by clicking the close box. Access asks whether you want to save the layout of the tables. Click Yes.

Identifying Dependencies

When you begin to create relationships among tables, you create what s known as dependencies ” that is, some tables, queries, forms, and reports rely on others to provide them with the necessary information. You can check the type and number of dependencies for an object you re thinking about changing by right-clicking the object in the Database window, and choosing the Object Dependencies option. The Object Dependencies task pane appears, listing the various items in your database, and showing you where dependencies exist (see Figure 17-8). You can show either those objects that depend on the selected item, or objects on which the selected item depends.

See Also  

Chapter 18, Preparing and Printing Reports in Microsoft Office Access 2003, rounds out your introduction to Access by showing you how to generate reports.

Backing Up Your Database

Access 2003 can easily create a backup copy of important database files by using the Back Up Database command directly from the File menu. When you choose the command, Access displays the Save Backup As dialog box, and names the database file with the current date, so that future backups do not overwrite previous versions (see Figure 17-9).

Figure 17-9: The new Back Up Database option enables you to easily create a backup copy of important files.