Creating 3 Related Tables

Creating 3 Related Tables

Creating 3 Related Tables

For the examples, we are going to create a database to be used by the HE department atcollege to track students enrolled onto their course. Open up Access, select new blank database. Because databases are so big, unlike other applications you have to save them before you can use them, use the folder icon to save to your memory stick or to your area and change the File Name to Courses and click create.

Creating Tables

Access automatically opens up with a new table. Before you go any further, change to design view, access prompts you to rename the table, call the table tblCourse.

In design view create the following field names, selecting the data types and setCourse Code to the primary key. Once you are happy with the data types and lengths you can now change the format of each field. Switching back to Datasheet View, you are prompted to save the changes to the table. It might be worth entering 1 row of data to check it works well.

Now create two more tables in the same database by clicking the ‘create’ tab and selecting ‘table’. Create the following tables setting the data types and length as appropriate. What data type areStudent House Number and Student Telephone? Which format for date looks best?

StudentStudent Number, use Autonumber

Student Title,

Student First Name,

Student Surname,

Student Date of Birth,

Student Gender,

Student Street,

Student Town,

Student City,set default to Stoke-on-Trent

Student Postcode,

Student Telephone

EnrolmentStudent Number, set the data type the same as on the Student table

Course Code, set the data type the same as on the Course Codes table

Enrolment Date,

Enrolment Statusset the default to Current

Navigate between the tables using the tabs. Don’t forget, you can only make changes to the table design when in design view and you can only enter records when in datasheet view.

Primary Keys

A primary key is a unique identifier, for example National Insurance Number or Car Registration. You may have many Students called Paul Smith, but by assigning each Student a unique number, you will be able to identify which particular Paul Smith you are referring to.

To assign a primary key or to remove a primary key, in design view, right-click in the column next to the field name and choose ‘Primary key’ from the list.Or use the Primary key Icon.

Assign a primary key to each table:

Course Codesin Course

Student Number in Student,

For the Enrolment table you need to create a Composite Primary Keyusing

StudentNumber and Course Code

Highlight both fields and select Primary key icon

If you leave the data type as Autonumber access will fill in the numbers starting at 1.

If you need letter identifiers, set the data type to text. If it is numbers only, set it to number. Remember - you CAN have numbers in a text data type, but you CANNOT have letters in a number data type.

Create Table Relationships

When you have designed and built your tables, you need to tell Access how each table relates to another. To make relationships between tables, the field names and data types must be identical.

  1. Check data types and sizes match and close all tables.
  2. Select Database Tools from the menu bar, click on the Relationship icon.
  3. When the dialogue box appears, select each tables and click add.
  4. All three tables then appear in the relationship window and you can close the previous dialogue box.
  1. Drag the Student Number from Student table to the StudentNumber on Enrolment table, this creates the relationship of the primary keyin the first table to a foreign key in the second table.
  2. The Edit Relationships dialog box appears; put a tick mark in the ‘Enforce Referential Integrity’ box and then click create.
  1. Follow the same steps for the Course Code.
  2. You should now have the following relationship. Close Relationships, saving changes.

Invent and enter 4 courses and at least 10student records.

Create tablesModified by Heather Skelton1