Access databases are relational databasesthe tables in the database can relate or connect to other tables through common fields. This type of database avoids redundant data, helps reduce errors, and saves space. Each table in a database contains information about one subject. For example, in your Fundraising database, one table contains data about the pledges that have been made and the second table contains data about the club members who are participating in the fundraiser. Each table has a primary key field, which ensures that the same record is not recorded more than one time in the table.
After you have set up different tables for each subject in your database, you must provide a way to bring that data together to create meaningful information. To do this, first you must define relationships. A relationship is an association that is established between two tables using common fieldsfields that contain the same data in more than one table. After you have established the relationship, you can create queries, forms, and reports to display information from several tables at once.
To identify which student received each pledge, you must connect the data in the 1B Pledges table with the data in the 1B Club Members table. The common field between these two tables is Student#. It is the field that appears in both tables.
In the 1B Club Members table, Student# is the primary key fieldensuring that a single student can appear in the table only one time. In the 1B Pledges table, each record includes the student number of the student who collected the pledge. Because a single student can obtain many different pledges, a students number could appear many times in the 1B Pledges table. The relationship between each student and the pledges he or she obtains is known as a one-to-many relationshipone student can obtain many pledgesand is the most common type of relationship in Access.
To create a relationship, the two connected fields must have the same data type and the same field size, but they need not have the exact same field name.
Be sure both tables are closed. On the Database toolbar, click the Relationships button , and then compare your screen with Figure 1.38.
The Relationships window opens and the Show Table dialog box displays. Here you identify the tables between which you want to create a relationship. Although this database currently has only two tables, larger databases can have many tables.
NoteIf the Show Table Dialog Box Does Not Display
If the Show Table dialog box does not display, click the Show Table button on the Relationship toolbar.
In the Show Table dialog box, with your 1B Club Members table selected, click Add.
In the Show Table dialog box, double-click your 1B Pledges table.
Alert!: Are There More Than Two Tables in the Relationships Window?
Removing Extra Table Field Lists
If you add one of the tables more than once, you must remove it from the Relationships Window. Right-click the duplicate table and then from the shortcut menu, click Hide Table, or from the Edit menu click Delete.
Close the Show Table dialog box. In the 1B Pledges field list, position your mouse pointer over the lower edge of the list to display the Vertical Resize pointer , and then drag downward to display the entire field list.
In the 1B Pledges field list, position your mouse pointer over the right edge of the list to display the Horizontal Resize pointer , and then drag right to display the entire name in the title bar. Point to the title bar of the field list you just resized and drag it to the right side of the window.
In the 1B Club Members field list, position your mouse pointer over the right edge of the list to display the Horizontal Resize pointer , and then drag right to expand the list to display the entire name in the title bar. Position the expanded field lists as shown in Figure 1.39.
In the field list for your 1B Pledges table, click Date Collected. With Pledge# deselected, notice that the Pledge# field displays in bold, and that in your 1B Club Members field list, the Student# field name displays in bold. Notice also that Student# is a field name in both tables.
In your 1B Club Members field list, click Student#, drag to the right to the 1B Pledges field list until your mouse pointer is on top of Student# as shown in Figure 1.40, and then release the mouse button.
In the displayed Edit Relationships dialog box, click to select the Enforce Referential Integrity check box, and then compare your screen with Figure 1.41.
In the Edit Relationships dialog box, click Create, and then compare your screen with Figure 1.42.
A join linethe line joining two tablesdisplays between the two tables. On the line, 1 indicates the one side of the relationship, and the infinity symbol () indicates the One club member can collect many pledges. These symbols display when referential integrity has been enforced.
Table relationships provide a map of how your database is organized, and you can print this information as a report. In this activity, you will print your relationships report.
With the Relationships window open, on the menu bar, click File, and then click Print Relationships. Compare your screen with Figure 1.43.
A report of the relationships you just created displays.
Check your Chapter Assignment Sheet or Course Syllabus, or consult your instructor, to determine whether you are to submit the printed pages that are the results of this project. To print, on the toolbar, click the Print button .
On the report title bar, click the Close button , and then click Yes when prompted to save the report.
In the displayed Save As dialog box, click OK to accept the default report name.
In the Relationships window title bar, click the Close button . If a message box displays asking if you want to save the layout of the relationships, click Yes.
More Knowledge: Other Types of Relationships
Using One-to-One and Multiple One-to-Many Relationships
There are other relationships that can be created using the same process in the Relationships window. The type of relationship is determined by the placement of the primary key field. A one-to-one relationship exists between two tables when a record in one table is related to a single record in a second table. In this case, both tables use the same field as the primary key. This is most often used when data is placed in a separate table because access to the information is restricted. You can also create multiple one-to-many relationships between tables in a database simply by adding more tables to the Relationships window and creating a join line between the tables based on their common field. A primary key field from one table can be joined to the same field in more than one table.
GO! with Microsoft Office 2003 Brief (2nd Edition)
Practical Intrusion Analysis: Prevention and Detection for the Twenty-First Century: Prevention and Detection for the Twenty-First Century