Locked lesson.

Upgrade

About this lesson

Start the process of creating a relationship between two tables by setting up a link table.

Quick reference

Creating a Link Table

Create a link table to assist with creating relationships between tables. 

When to use

We create a link table whenever we want to create a relationship between two tables that do not have common fields. 

Instructions

Creating a Table

  • From the Create tab, in the Tables group, select Table Design. 

A new table will be created with the generic name 'Table1' and it will contain no fields. Our table needs to contain one field, 'Status'. 

  • Click in the FieldName field. 
  • Type 'Status' and press TAB. 
  • In the Data Type field, select Short Text. 
  • In the Description field, type 'Status for this ticket'. 
Setting a Primary Key Field
  • Click on the 'Status' field. 
  • From the Table Design tab, in the Tools group, click Primary Key. 
Setting Field Properties
  • Set the Field Size to 30. 
  • Set the Required field to Yes. 
  • Set the Allow Zero Length field to No. 
  • Set the Indexed field to Yes (No Duplicates). 
  • Click the cross to Close. 
  • Type a name for the table, 'tblStatus', and click OK. 

Adding Records to the Table

  • Double-click on 'tblStatus' to open it. 
  • Click in the 'Status' field and type 'Active'. 
  • Click in the cell below and type 'Pending'. 
  • Click in the cell below and type 'Closed'. 
  • Click in the cell below and type 'On-Hold'. 
  • Close all open tables. 

Relationships

To create relationships between tables, we need to be able to see all tables and fields. 

  • From the Database Tools tab, in the Relationships group, click Relationships. 
  • In the Add Tables pane on the right-hand side, select both tables. 
  • Click Add Selected Tables. 
  • Drag and drop to move the tables. 

Relationships can be created between tables via common fields. In our example, the two tables do not share a common field so we need to create a link table. The link table will contain a field from 'tblTicket' and a field from 'tblStatus' and we will use this table to create a relationship. 

Creating a Link Table

  • From the Create tab, in the Tables group, click Table.

When we select Table instead of Table Design, we automatically get an ID field in the table. 

  • From the Home tab, in the Views group, click View. 
  • Select Design View. 
  • Name the table 'tblTicketStatus'. 
ID Field
  • Click in the Description field and type 'Unique ID of this table'.
TicketID Field
  • In the Field Name field, type 'TicketID'. 
  • In the Data Type field, choose Number. 
  • In the Description field, type 'Unique ID (Primary Key) of the ticket table'. 
Status Field
  • In the Field Name field, type 'Status'. 
  • In the Data Type field, choose Short Text. 
  • In the Description field, type 'Unique ID (Primary Key) of the status table'.

Set the field properties for the new fields. 

  • Go back to the Relationships tabs. 
  • In the Add Tables pane on the right-hand side, select 'tblTicketStatus'. 
  • Click Add Selected Tables. 
  • Position the new table in between the existing tables. 

Hints & tips

  • It is not always necessary to create a link table. If the tables we want to link already have common fields, a link table is not required. 
Login to download
  • 00:03 In this lesson, we're going to create a second table and
  • 00:07 then we're going to link the second table to table ticket using a link table.
  • 00:13 So let's just very quickly refer back to our ticket example because
  • 00:18 there is one field in here that I didn't add in to our ticket table and
  • 00:23 that field is the status field down here.
  • 00:26 So the status field will show the status of this ticket, so
  • 00:30 whether it is active, pending, closed or on hold.
  • 00:34 And a ticket could have more than one status.
  • 00:37 For example, this ticket is active, but it's pending.
  • 00:41 The ticket could be active and on hold.
  • 00:44 So what I'm going to do is I'm going to create a table just to hold these
  • 00:48 different status options.
  • 00:50 So let's minimize and go back to our tables.
  • 00:53 Now the first thing I'm going to do here is simply close the table
  • 00:56 that we have open by clicking on the cross on the tab.
  • 00:59 Let's go up to the create group, and we're going to use the table design option.
  • 01:04 Now this is different from selecting the table option because
  • 01:08 it doesn't automatically give you an ID field.
  • 01:11 So effectively we don't automatically have a primary key field in this table
  • 01:15 when we use the table design option.
  • 01:18 Now that's fine for this case, let's type in our field.
  • 01:22 Now this table is only going to have one field and that field is status.
  • 01:28 And the data type for this is going to be short text.
  • 01:32 Let's add a description, Status for this ticket.
  • 01:39 Let's take a look at our field properties.
  • 01:42 Well, the field size I definitely don't need 255 characters,
  • 01:45 I'm going to go for 50.
  • 01:47 Is it required?
  • 01:48 Yes.
  • 01:49 Do we allow zero length?
  • 01:51 No.
  • 01:51 Is this going to be indexed?
  • 01:53 Yes ,it is.
  • 01:54 So let's switch into datasheet view, we're going to be asked to save this table.
  • 02:00 Let's click on yes to save and we're going to stick with our naming convention.
  • 02:05 So TBL is the prefix and I'm going to call this table status, and click on okay.
  • 02:13 Now notice I'm getting an error message here, there is no primary key defined.
  • 02:17 And that is absolutely correct.
  • 02:18 I've forgotten to do that.
  • 02:20 So let's click cancel on this message.
  • 02:22 Let's select the only field that we have, the status field,
  • 02:26 and let's make this the primary key field by clicking the primary
  • 02:29 key button on the table design ribbon.
  • 02:32 Now, let's switch back into datasheet view.
  • 02:35 I can now add into this field the different statuses.
  • 02:38 So we have active is the first one.
  • 02:41 The next one is pending.
  • 02:43 Then we have on hold, and the final one is closed.
  • 02:49 And that is all I'm going to have in this table.
  • 02:52 So now I have two tables that I want to link together.
  • 02:56 Now I'm going to quickly close this table down because if you want to
  • 02:59 see the relationships that you have created between any of your tables,
  • 03:03 you need to jump into database tools and click on that relationships button.
  • 03:08 And this will take you into this more diagram style format.
  • 03:12 Now currently, I can't see any of my table showing in this window.
  • 03:16 But if we look over on the right hand side at the add tables pane, I can select both
  • 03:21 of my tables and then click add selected tables to put those in the middle.
  • 03:27 So this allows me to see the tables and all of the fields.
  • 03:32 And if this isn't quite long enough, I can drag the bottom down just to resize.
  • 03:37 Now this is where I would come to create links between tables.
  • 03:41 But currently, taking a look at all of the fields in table ticket,
  • 03:45 I don't have a field that is common between this table and table status.
  • 03:50 I only have one field in table status, but
  • 03:53 I don't have a status field in table ticket.
  • 03:57 So because there is no common field that I can use to link these two,
  • 04:01 I'm going to need to create a little table in the middle here that contains one field
  • 04:06 from table ticket and the status field as well.
  • 04:10 So this is going to require us to create yet another table.
  • 04:14 So let's close down their relationships, so
  • 04:17 I'm going to say yes to save the layout, back up to create and this time
  • 04:22 I'm going to use the table option just here because that gives me my ID field.
  • 04:27 But let's jump into design view.
  • 04:29 I'm going to be asked to give my table a name immediately.
  • 04:33 So this one is going to be called table ticket status.
  • 04:38 Click on OK.
  • 04:41 So now I have that ID field.
  • 04:43 I need to grab a field from table ticket.
  • 04:47 So let's open table tickets so we can review what we have.
  • 04:51 So maybe the column that I want to use is the ID column of the table ticket table.
  • 04:57 And if I go back to tickets status, I already have an ID column.
  • 05:02 So I'm going to need to give this a slightly different name so
  • 05:06 I'm going to call this ticket ID.
  • 05:08 The data type in here is going to be number.
  • 05:11 And let's add some descriptions.
  • 05:13 And this one is going to be unique ID and
  • 05:17 we're going to save primary key of the ticket table.
  • 05:22 And then the final thing that we want to add is the only field that we have
  • 05:27 in the status table which is status.
  • 05:31 So our third field here for this link table is going to be status,
  • 05:36 it's a short text field and this is the unique ID, the primary,
  • 05:45 Key of the status table.
  • 05:49 Just so we can differentiate all of those different field types.
  • 05:53 Now the primary key of this link table is the ID field.
  • 05:57 And the other two are the primary keys from the other two tables.
  • 06:01 Let's make sure our field properties are correct.
  • 06:03 So if we start with ticket ID, I'm going to say required, yes, indexed, yes.
  • 06:09 And for status, we're going to say let's just take that field size down to 50.
  • 06:15 Required, yes, allow zero length, no, indexed, yes.
  • 06:20 Perfect.
  • 06:22 Let's close down all of our tables.
  • 06:26 Yes, I want to save.
  • 06:27 So now if we jump back into relationships,
  • 06:31 I need to add that link table that I've just created.
  • 06:36 So over in the add tables pane, I'm going to select table ticket status and
  • 06:40 add selected tables.
  • 06:42 Now you can arrange these however you like.
  • 06:44 I generally like to put my link table in the middle.
  • 06:47 And now we're at the stage where we can link all of our fields together,
  • 06:51 which we'll be doing in the next lesson.

Lesson notes are only available for subscribers.

Gift this course