Relationships between Tables

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 Street Address, Suburb, and Postcode for the same reason.
    Suburb
    Postcode
    Phone

The Subjects table will have fields:

    SubjectID  a unique code for each subject. (Use the school's subject code)
    Subject    full title of the subject
    Notes      comments or a brief description of what this subject covers.

The Grades table will then have just three fields:

    StudentID  a code that ties this entry to a student in the Students table
    SubjectID  a code that 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 2007 and 2010, Relationships is on the Database Tools ribbon. In Access 95 - 2003, Relationships is on the Tools menu (or the Editmenu in Access 1 -2.) "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.

Technically, the process of breaking your data down into related tables is called normalization. If you wish to read the technical rationale