Relationships between tables

By Allen Browne, Access MVP

Database beginners sometimes struggle with what tables are needed, and how to relate one table to another. It's probably easiest to follow with an example.

As a school teacher, Margaret needs to track each student's name and home details, along with the subjects they have taken, and the grades achieved. To do all this in a single table, she could try making fields for:

Name

Address

Home Phone

Subject

Grade

But this structure requires her to enter the student's name and address again for every new subject! Apart from the time required for entry, can you imagine what happens when a student changes address and Margaret has to locate and update all the previous entries? She tries a different structure with only one record for each student. This requires many additional fields - something like:

Name

Address

Home Phone

Name of Subject 1

Grade for Subject 1

Name of Subject 2

Grade for Subject 2

Name of Subject 3

But how many subjects must she allow for? How much space will this waste? How does she know which column to look in to find "History 104"? How can she average grades that could be in any old column? Whenever you see this repetition of fields, the data needs to be broken down into separate tables.

The solution to her problem involves making three tables: one for students, one for subjects, and one for grades. The Students table must have a unique code for each student, so the computer doesn't get confused about two students with the same names. Margaret calls this field StudentID, so the Students table contains fields:

StudentID a unique code for each student.

Surname split Surname and First Name to make searches easier.

FirstName

Address split address information for the same reason.

Suburb

Postcode

Phone

The Subjects table will have fields:

SubjectID a unique code for each subject.

Subject full title of the subject

Notes brief description of what this subject covers.

The Grades table will then have just three fields:

StudentID ties this entry to a student in the Students table

SubjectID ties this entry to a subject in the Subjects table

Grade the mark this student achieved in this subject.

After creating the three tables, Margaret needs to create a link between them. In Access 95 - 2003, she would select the Database Container window, and choosing Relationships from the Tools menu (the Edit menu in Access 1 -2.) In Access 2007, Relationships is on the Show/Hide group of the Database Tools ribbon. "Grades" will be the "related table" in relationships with both the other tables.

Now she enters all the students in the Students table, with the unique StudentID for each. Next she enters all the subjects she teaches into the Subjects table, each with a SubjectID. Then at the end of term when the marks are ready, she can enter them in the Grades table using the appropriate StudentID from the Students table and SubjectID from the Subjects table.

To help enter marks, she creates a form, using the "Form/Subform" wizard: "Subjects" is the source for the main form, and "Grades" is the source for the subform. Now with the appropriate subject in the main form, and adds each StudentID and Grade in the subform.

The grades were entered by subject, but Margaret needs to view them by student. She creates another form/subform, with the main form reading its data from the Students table, and the subform from the Grades table. Since she used StudentID when entering grades in her previous form, Access links this code to the one in the new main form, and automatically displays all the subjects and grades for the student in the main form.

Allen Browne is a Most Valued Professional (MVP) for Access. Mr. Browne is the owner of Allen Browne's Database and Training, and he lives in Perth, Australia.