Locked lesson.

Upgrade

About this lesson

Create a third table to hold the company information and then create a link table to link the ticket table to the company table. 

Quick reference

Setting Up Relationships

Create a table that holds the company information and a link table to link the ticket table to the company table.  

When to use

Create link tables whenever you need to create relationships between tables that have no shared field. 

Instructions

Creating Another Table

We are going to create a table that holds the 'Company' information. Download the file 'Company_List.txt'. 

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

Our table will contain one field.

  • In the Field Name, type 'Company'. 
  • In the Data Type field, select Short Text. 
  • In the Description field, type 'Company Name'. 

Set the Primary Key field for the table. 

  • Select the 'Company' field. 
  • From the Table Design tab, in the Tools group, click Primary Key. 

Set the Field Properties. 

  • Field Size: 30. 
  • Required: Yes.
  • Allow Zero Length: No. 
  • Indexed: Yes (No Duplicates).
  • Close the table and Save the changes. 
  • Name the table 'tblCompany'.
  • Click OK

Adding Data to the Table

  • Double-click to open the table 'tblCompany'. 
  • Use the file 'Company_List.txt' to populate the table. 
  • Close the table. 

Creating Relationships

Next, we need to create relationships between our tables. 

  • From the Database Tools tab, in the Relationships group, click Relationships. 
  • From the Add Tables pane, select both tables and click Add Selected Tables. 

The tables do not share a common field so we need to create a link table. 

  • From the Create tab, in the Tables group, click Table.
  • Right-click on the tab and select Design View. 

When we create a table in this way, an ID field is automatically created which provides a unique way of identifying each record. 

  • Add a description for the ID field, 'Unique ID for this table'. 
  • Save the table as 'tblTicketCompany'.
  • Click OK. 

We need to include a field from 'tblTicket' and a field from 'tblCompany' in our link table. The second field will be the ID field from 'tblTicket'. As we already have an 'ID' field in 'tblTicketCompany' we will call this something different. 

  • In the Field Name, type 'TicketID' in 'tblTicketCompany'. 
  • In the Data Type field, select Number. 
  • In the Description field, type 'Unique ID (Primary Key) of the ticket table'. 
  • In the Field Name, type 'Company'.
  • In the Data Type field, select Short Text. 
  • In the Description field, type 'Unique ID (Primary Key) of the company table'. 
  • Set the field properties for each field. 

Creating Relationships

We can now create a relationship between 'tblTicket' and 'tblCompany' using the 'tblTicketCompany' link table. 

  • From Database Tools, in the Relationships group, click Relationships. 
  • In the Add Tables pane, select 'tblCompany' and 'tblTicketCompay'. 
  • Click Add Selected Tables. 
  • Drag the 'ID' field from 'tblTicket' to the 'TicketID' field in 'tblTicketCompany'. 
  • In the Edit Relationships window, select Enforce Referential Integrity. 
  • Click Create. 
  • Drag the 'Company' field from 'tblCompany' to the 'Company' field in 'tblTicketCompany'. 
  • In the Edit Relationships window, select Enforce Referential Integrity. 
  • Click Create.
Login to download
  • 00:04 So we finished out the last lesson by adding our link table into our
  • 00:08 Relationships window.
  • 00:09 And now we need to link all three tables together using our link table
  • 00:14 in the middle.
  • 00:15 And creating relationships between tables is super easy when you're
  • 00:20 working in this view.
  • 00:21 So what is the first link that I want to create?
  • 00:24 Well, I want to link tblTicket to tblTicketStatus.
  • 00:28 Now, I'm going to do that using the ID field in the tblTicket table.
  • 00:32 Now remember, we don't link it to the ID field in tblTicketStatus,
  • 00:37 we link it to TicketID, because this is effectively this field,
  • 00:43 but because we couldn't call it ID, we named it TicketID.
  • 00:47 So all we do is drag ID across to TicketID, and
  • 00:51 it's going to pop open the Edit Relationships window.
  • 00:55 Now, when you get to this,
  • 00:57 just check to make sure that it's linking the correct fields, ID to TicketID.
  • 01:01 And then we have a few different options, and
  • 01:04 I would always advise that you select Enforce Referential Integrity.
  • 01:07 And I'm going to show you an example of what that does a bit later on.
  • 01:11 But basically, if I were to delete a record that is linked to other records in
  • 01:16 other tables, it's basically going to delete all linked records as well,
  • 01:21 so that we don't have any orphan records leftover.
  • 01:25 We do have other options here, such as Cascade Delete, but
  • 01:28 we are going to Enforce Referential Integrity.
  • 01:31 Also notice the relationship type at the bottom, One-To-Many.
  • 01:36 So what that does mean?
  • 01:37 Well, it means that one ticket could have many statuses.
  • 01:41 Lets click on Create, and there you can see that link.
  • 01:45 It's worth noting that if you want to make any changes, you can double click to pop
  • 01:50 that window open again and you can go in and make your modifications.
  • 01:54 Let's do the same with the status field in tblStatus.
  • 01:57 This is a bit more straightforward, we can simply link status to status.
  • 02:02 So let's drag and drop.
  • 02:04 Again, it's going to pop open that box, check the fields that you're linking,
  • 02:09 Enforce Referential Integrity, and click on Create.
  • 02:13 And then we've managed to link two tables via a linked table.
  • 02:17 Now, we're going to repeat this process,
  • 02:19 because we are going to add another table into our database.
  • 02:22 And this table is going to hold the company information for our customers.
  • 02:27 Now, you'll find the list of companies to add in the course files folder
  • 02:31 in the file Company_list.txt.
  • 02:33 So we're going to do this in pretty much the same way that we set up
  • 02:38 the status table.
  • 02:39 So let's go up to Create and we're going to choose Table Design.
  • 02:44 This is only going to contain one field and that is the company field.
  • 02:47 This is a short text field, let's add in a description,
  • 02:52 which is simply going to be Company Name.
  • 02:55 Let's check our field properties, do I need 255 characters?
  • 03:01 Well, probably not, I'm going to say 50.
  • 03:03 Is it required?
  • 03:04 Yes, it is.
  • 03:05 Are we allowing zero length?
  • 03:07 Nope.
  • 03:07 Are we indexing?
  • 03:09 Yes, we are.
  • 03:11 And because this is the only field in the table,
  • 03:14 remember we need to make this the primary key field.
  • 03:17 Now, if I attempt to switch to Datasheet View to add my companies, I
  • 03:23 need to save my table, and we're going to call this tblCompany, and click on OK.
  • 03:29 I'm now going to take the list of companies that you just saw in that
  • 03:33 text file and add them in.
  • 03:35 So join me back here in a couple of seconds.
  • 03:38 So those are my companies, let's close down this table.
  • 03:41 Now, if I go back into my relationships and add the new table,
  • 03:46 tblCompany, currently, this isn't linked to anything.
  • 03:50 Now, I need to link tblCompany to tblTicket.
  • 03:55 But again, we don't have a company field in here to link these two tables together.
  • 04:01 So we're going to need to create another link table,
  • 04:04 which contains a field from tblTicket and a company field.
  • 04:08 So let's close down, let's save the layout.
  • 04:11 I'm going to go to Create, and
  • 04:13 this time we're going to use the table option because I want an ID field in here.
  • 04:18 Let's right click and immediately switch to Design View.
  • 04:21 We're going to be asked to save and give our table a name.
  • 04:24 So this one is going to be tblTicketCompany.
  • 04:29 I generally like to put in the name the two tables that I'm linking together.
  • 04:32 Let's click on OK.
  • 04:34 So description for this first field is the unique ID for this ticket.
  • 04:39 And once again, I need to add a field from tblTicket.
  • 04:43 So I'm going to use the ID field again.
  • 04:46 But remember, we can't call it ID, because we already have an ID field.
  • 04:51 So once again, we're going to call this TicketID.
  • 04:54 This is going to be a number date type and
  • 04:58 this is the unique ID, Primary key,
  • 05:07 For the ticket table.
  • 05:10 And then the only other field that we need to add is one from tblCompany.
  • 05:16 And we only have one field in here, so that makes it very simple,
  • 05:19 it's the company field.
  • 05:21 Let's click in the data type, it is a short text data type, and
  • 05:28 this is the unique ID (Primary Key) for the company table.
  • 05:35 Once again, let's make sure that we're setting the correct field
  • 05:39 properties required, yes, allows zero length, no, indexed, yes.
  • 05:44 And also for TicketID, I think we're pretty good here,
  • 05:48 we just want to change that to yes.
  • 05:50 Excellent, so now, I have my three tables.
  • 05:53 Let's jump back into our Relationships window.
  • 05:57 I need to add the link table that I've just created, tblTicketCompany,
  • 06:02 and now I can go about linking these two tables via the link table.
  • 06:07 So from tblTicket, I want the ID field and I'm dragging and
  • 06:10 dropping that onto TicketID.
  • 06:12 Once again, check your fields, Enforce Referential Integrity,
  • 06:16 and click on Create.
  • 06:18 And then the final link we need to do here is just to link the company
  • 06:23 field from tblCompany to the company field in tblTicketCompany.
  • 06:28 Check your fields, Enforce Integrity, and click on Create.

Lesson notes are only available for subscribers.

Gift this course