Primary Key

Before we can create “Relationships” between tables, we must first create the tables. If you do not know what tables are, just go back to Lesson 7 to know what kind of tables are needed, and what fields they should have.

We have a “Song” table, with the fields “Song Title”, “SingerID”, “AlbumID”, “Duration”, “Genre” and “Date” and a “Lead Singers” table, with the fields “SingerID”, “Last Name”, and “First Name”.

Let us first start with the “Lead Singers” table.
To create a table, we have two possibilities:
1. you can create them in “Datasheet View”, or
2. you can create them in “Design View”.

Because I have already created the first table (“Album”) in “Datasheet View”, I now choose “Design View”.

Select the “Create” tab in the “Ribbon” and click the “Table Design” button.

I give the various name fields with their “Data type”. The “SingerID” is “AutoNumber” and the other two are “Text” fields. When we create a table in “Design View”, we need to manually set a “Primary key”, if needed.

I choose the “SingerID” field, and click the “Primary Key” in the “Ribbon” because no two singers will have the same ID. An icon of a key will appear next to the field name.

If you made a mistake, or if you want to change the “Primary key”, no problem. Select the field you have set as “Primary Key” and click the “Primary Key”. The icon will disappear. Choose the appropriate field and then click the “Primary Key”.

If everything is set up correctly, click the “Save” button in the “Quick Access” toolbar.

In the dialog that appears, you give the table a name, in this case “Lead Singers”, and click the OK button.

When you have done everything correctly, this table should now appear in the “Navigation pane”.

We repeat this for the “Song” table.
In the “Song” table, we need the following fields: “Song Title”, “SingerID”, “AlbumID”, “Duration”, “Genre” and “Date”.

For all fields I choose “Text” as the “Data type”, with the exception of the fields “SingerID” and “AlbumID” which still consist of numbers. The “Date” field consists of 4 digits (ex.1992) or the letters “na” if it is not known. “Data type”, “Text” is fine for this.
When I want to save this table, a warning window stating that I have not set the “Primary key” will appear. I have two choices: I let “Access” create one for me, or I create one.

Because it is safer to work in each table with a “Primary key”, I choose the first option and I click “Yes”. Then ‘Access’ automatically creates a field with “Data type” as “Autonumber” and makes it as the “Primary key”. You can still change this field name to something more appropriate.

This is what you should have:

Three tables: 1 “Album”, 2 “Lead Singers” and 3 “Song”.
In the next lesson we are going to connect these with each other.

Remember this:
– If you wish to insert a row between two existing rows, select the row above which you want to insert a row, and click the “Insert Rows” button in the “Ribbon”.
– If you wish to delete a row, select the row, and click the “Delete Rows” button in the “Ribbon”.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats