Locked lesson.

Upgrade

About this lesson

Make changes to the link table and understand the types of error messages that can occur. 

Quick reference

Editing the Link Table

Understand some issues that may come up when editing link tables and how to resolve them. 

When to use

Edit a link table whenever you need to add new information or make a change. 

Instructions

  • Open the table 'tblTicketCompany' and review the fields. 

First, let's create a drop-down list for the Companies List. 

Troubleshooting Issues (Changing Data Types)

  • Right-click on the tab and select Design View. 
  • From the 'Company' field, click the drop-down arrow in Data Type. 
  • Select Lookup Wizard from the list. 

We will receive an error message as we cannot change the data type for fields that have existing relationships with fields in other tables. 

Deleting Relationships
  • From the Database Tools tab, in the Relationships group, select Relationships. 
  • Select the relationship between 'tblTicketCompany' and 'tblCompany'. 
  • Press Delete. 
  • Select the relationship between 'tblTicketCompany' and 'tblTicket'. 
  • Press Delete.

Editing the Link Tables

Let's create a drop-down to list the Companies. 

  • Open the table 'tblTicketCompany'.
  • Right-click on the tab and select Design View. 
  • Change the Data Type for the 'Company' Field to Lookup Wizard. 
  • Select I want the lookup fields to get their values from another table or query. 
  • Click Next.
  • Select Table: tblCompany. 
  • Click Next. 
  • Select the 'Company' field and click the chevron to move it to the Selected Fields area. 
  • Click Next. 
  • Sort by the 'Company' field. 
  • Click Next. 
  • Check the preview looks correct. 
  • Click Next. 
  • Select Enable Data Integrity and Restrict Delete. 
  • Click Finish. 
  • Save the table. 

Let's refresh the Relationships window. 

  • Close down the Relationships window. 
  • From the Database Tools tab, in the Relationships group, select Relationships. 

Notice that Access has automatically created a link between 'tblTicketCompany' and 'tblCompany'.

Creating a Drop-down List with Two Fields. 

In order to add records to 'tblTicketCompany', it would be helpful to create another drop-down list to select the TicketID. However, it is quite hard to determine the Company simply based off of the TicketID. We can add the customer name as a reference point to make this easier. 

  • Open the table 'tblTicketCompany'. 
  • Right-click on the tab and select Design View. 
  • Change the Data Type for the 'TicketID' field to Lookup Wizard. 
  • Select I want the lookup fields to get their values from another table or query. 
  • Click Next.
  • Select Table: tblTicket. 
  • Click Next. 
  • Select the 'TicketNumber' and the 'CustomerName' field and click the chevron to move them to the Selected Fields area. 
  • Click Next. 
  • Leave the sort order field blank. 
  • Click Next. 
  • Check the preview looks correct. 
  • Click Next. 
  • Select Enable Data Integrity and Restrict Delete. 
  • Click Finish. 
  • Save the table. 

Let's refresh the Relationships window. 

  • Close down the Relationships window. 
  • From the Database Tools tab, in the Relationships group, select Relationships. 

Notice that Access has automatically created a link between 'tblTicketCompany' and 'tblTicket'.

  • Add records to 'tblTicketCompany'. 

Adding Records to a Link Table

Now, let's add some records to the other link table, 'tblTicketStatus'. 

  • Double-click to open 'tblTicketStatus'. 

We could use the same process and create drop-down lists to input the records. For this example, we will enter data manually. 

If we refer to the 'Ticket_Example.txt' file, the ticket '123456' has a status of 'Active' and 'Pending'. Records can have multiple status's. As there are two, we need to ensure that we add both records to the table for this ticket. 

  • Type '1' into the 'TicketID' field.
  • Type 'Active' into the 'Status' field. 
  • Type '1' into the 'TicketID' field. 
  • Type 'Pending' into the 'Status' field. 

Carry on adding as many records as necessary for each ticket. 

There is a much more efficient way of doing this using a form. We will look at this in later lessons.

Login to download
  • 00:03 In this lesson, we're going to ramp things up a notch.
  • 00:08 And we're going to take a look at how we can make edits to our link table.
  • 00:12 And I'm also going to show you some of the issues that might arise when you start
  • 00:17 doing things like this.
  • 00:18 So if you recall in the last lesson we left off simply by linking our
  • 00:23 company table to trouble ticket via the tblTicket company table.
  • 00:28 Now if I open a tblTicket company,
  • 00:30 notice that we have three fields, ID, ticket ID, and company.
  • 00:35 And we're going to need to add some records into here in order for
  • 00:39 this to start working correctly.
  • 00:41 Now for me to add records into this particular table,
  • 00:44 it's going to be quite difficult.
  • 00:46 Because I need to know the ticket ID, and
  • 00:49 then I need to know what company is assigned to that ticket ID.
  • 00:53 Now that is pretty hard to do.
  • 00:55 So what I might want to do here is add a drop down menu to this ticket ID field,
  • 01:00 that shows me the ticket ID, but
  • 01:02 also the customer name of the customer that owns that ticket.
  • 01:07 It's then a lot easier for me to work out which company that customer belongs to,
  • 01:12 particularly if I know my customer base.
  • 01:14 So what I want to do here is create two drop down lists,
  • 01:17 one that's going to allow me to select the company.
  • 01:20 And one that's going to allow me to select the ticket ID, but
  • 01:24 also shows the customer name that's associated with that ticket.
  • 01:27 So pretty complex stuff here for a beginners course.
  • 01:31 Now, we're going to start out by doing the simplest thing first which is adding
  • 01:35 a drop down menu to list our companies.
  • 01:38 And this is going to throw up the first issue that you might come across.
  • 01:41 So if I go into design view, and what I want to do here is,
  • 01:45 I want to create a drop down list.
  • 01:47 So I need to change the data type to lookup wizard.
  • 01:51 Now notice I get an error message, it says you can't change the data type or
  • 01:57 field size of this field as it's part of one or more relationships.
  • 02:02 If you want to change the data type,
  • 02:04 first delete its relationship in the relationships window.
  • 02:08 So what is that telling us?
  • 02:11 Well, if we go back to relationships, it's not letting me modify this
  • 02:16 field because we have links to other tables utilizing that field.
  • 02:21 So in order to be able to backtrack and do this, I mean, ideally,
  • 02:24 I would have set these up prior to linking these tables together.
  • 02:28 But if you do find yourself in this situation,
  • 02:31 all you really need to do is delete out the relationships and
  • 02:34 then set up the lookup and then re link everything afterwards.
  • 02:38 So i'm going to select the first relationship here between tool
  • 02:41 ticket company and table company, and just press the delete key.
  • 02:45 It's going to ask me if i'm sure, i'm going to say yes.
  • 02:48 It's recognizing that I have one of the tables open, which I do.
  • 02:52 So let's click on OK, close down the table.
  • 02:55 I'm going to say Yes to save the changes, and then we can delete.
  • 03:01 I'm also going to delete the link to tblTicket like so.
  • 03:07 So now these tables are linked, I should be able to open up tblTicket company,
  • 03:13 go into design view and change this datatype to lookup wizard.
  • 03:18 Which I can now this time when i'm working through the wizard,
  • 03:22 I do want to use a table to populate my drop down list.
  • 03:26 Because I have my companies listed our interval company.
  • 03:30 So i'm going to select the first option, click on Next.
  • 03:33 The one that I want is at the top table company, I want to view the tables and
  • 03:38 click on Next.
  • 03:39 Now my tbl company table only has one field, so
  • 03:43 we need to select this and then click on Next.
  • 03:46 And then I can choose if I want to sort this list.
  • 03:50 So do I want the companies sorted into alphabetical order?
  • 03:54 I can choose ascending or descending.
  • 03:56 If I apply none, it's just going to keep the list as it is.
  • 04:00 Now, i'm just going to choose to sort on the company field, so
  • 04:03 it uses the sort order that is in in the company table.
  • 04:06 Let's click on Next.
  • 04:08 I'm now getting a preview of the companies in that table which is fine.
  • 04:11 Let's click on Next.
  • 04:13 I'm happy to name my lookup field company.
  • 04:16 I'm going to enable data integrity, but
  • 04:19 i'm going to restrict delete, and then click on Finish.
  • 04:23 I'm going to say Yes, to save my table.
  • 04:26 And what you should find is, if we go back to relationships,
  • 04:30 close it down and say Yes, and reopen our relationships.
  • 04:34 Notice it's automatically created a link between these two tables for me.
  • 04:39 Based on the lookup wizard information, if I double click on this relationship,
  • 04:44 I can see company to company, one to many.
  • 04:46 So I haven't had to recreate that relationship.
  • 04:50 If I go back to tblTicket company and take a look at this in Datasheet View,
  • 04:55 when I click the drop down, I now have a list of all of those companies.
  • 04:59 Now I need to do something similar for ticket ID.
  • 05:03 But as I said, I want this drop down to show me two pieces of information,
  • 05:08 the ticket ID and the customer name.
  • 05:10 Now the Customer Name column is only going to be there for informational purposes.
  • 05:15 It's only there to make it easier for
  • 05:17 me to identify which ticket i'm choosing from the list.
  • 05:21 So let's jump back into Design View.
  • 05:24 And let's go to ticket ID and go to Look Up Wizard.
  • 05:29 Again, I want to use fields from a table because both the ticket ID or
  • 05:34 the ticket number and the customer name are in the ticket table.
  • 05:38 So let's click on Next.
  • 05:40 I need to select my table, which is going to be tblTicket, and
  • 05:43 then I can choose which fields I want to show in the dropdown.
  • 05:47 So I want to show the ticket number, but I also want to show the customer name.
  • 05:53 Let's click on Next.
  • 05:56 I can then choose how I want to order these.
  • 05:59 Now i'm not going to select anything in these fields because
  • 06:02 I just want them ordered as they are.
  • 06:04 Let's click on Next.
  • 06:05 And this is showing you what that's going to look like.
  • 06:08 And you can see right at the top there is the ticket that we've been working on,
  • 06:11 the ticket number and then the customer name.
  • 06:14 And then I have all of the rest.
  • 06:16 Let's click on Next again.
  • 06:18 I'm going to enable data integrity.
  • 06:21 And notice here which label would you like to use for your lookup field Ticket ID?
  • 06:26 So this is how access creates those relationships between the tables.
  • 06:31 Let's click on Finish.
  • 06:34 I'm going to save my table,
  • 06:36 i'm going to close down relationships just so that it refreshes.
  • 06:41 Let's open it up again, take a look at that,
  • 06:43 it's automatically created the other link for me.
  • 06:47 So really if I want to have a drop down, I would do that initially because then
  • 06:52 I don't need to go in and do the relationships myself.
  • 06:55 Let's go back to tblTicket company into data sheet view and
  • 06:59 see what that looks like.
  • 07:01 So now when I click in ticket ID, not only do I get the ticket number, but
  • 07:05 I also get a bit of reference information to make it easier for
  • 07:09 me to know which ticket i'm selecting.
  • 07:11 So we're going to say Claire Smith, and her company is Microworld.
  • 07:16 So let's choose that one from the drop down like so,
  • 07:20 I could carry on going, so let's add a few more.
  • 07:23 Now the final thing we need to do before we finish off this lesson and
  • 07:28 I'm just going to close down our tblTicket company table,
  • 07:33 is let's go back to tblTicket status.
  • 07:36 Because we don't currently have any information in here either, and
  • 07:40 I could go in and set up the same deal for these as well.
  • 07:43 So I could create drop downs to make it easier for me to add in the ticket ID and
  • 07:47 the status.
  • 07:48 Now that might be something that you want to do.
  • 07:51 I'm not going to do that just to save a bit of time as we've already practiced it,
  • 07:54 but I am going to add in some information just here.
  • 07:57 So let's look at our sample ticket again, so this is ticket ID number one.
  • 08:02 The customer name is Claire Smith and we need to add the status this time, and
  • 08:07 she has two statuses here of active and pending.
  • 08:10 So when you're adding these records in,
  • 08:13 if they have multiple items like this, you'll need to add two records for her.
  • 08:18 So the ticket ID here is ticket ID 1, the status or the first one is active,
  • 08:24 the next row of the record is also going to be ticket ID number 1.
  • 08:29 Because it's still for Claire.
  • 08:31 Because she has two status of active and pending.
  • 08:36 If she had three, I would add another number 1, or
  • 08:39 I can go on the next record, record number 2,that one might be closed.
  • 08:44 Maybe record number three is active, and also on hold.
  • 08:52 And we would carry on going through adding entries like this.
  • 08:55 Now there is a much better way of doing this and
  • 08:58 that much better way is by using a form.
  • 09:00 And we're going to get onto that a bit later in the lesson.

Lesson notes are only available for subscribers.

Gift this course