Understanding Access 2013 Relationships between Tables – Part 2

There are several features in Access 2013 that make tables much interactive and versatile. Lookup wizard allows you to predefine the set of values that a field can take. The potential values appear as a drop down menu, and you can simply select the correct value for that particular record. You can further choose to allow multiple values or not. In case you allow this, the values in the drop down menu would come with checkboxes that can be used to select multiple values. Access 2013 allows you to insert values manually or by looking up another table.

Watch the free video here, transcripts for the entire video follow:

Learn how to master Microsoft Access 2013. Get our 9-hour of Microsoft Access 2013 training – click here.

Video transcripts:

Hello again and welcome back to our course on Access 2013. In the previous section we created the first draft of a movies database and we created the movie table, put some data in it, and I left you with an exercise to do to create an actor table and put at least three actors in it. I’ve got a copy of my answer to that in front of me here. My answer was example-02 in the files that you get with the course. This is a copy of that. There are the two tables. I want to look at the actor table first. So if I want to look at the data I just double click and it opens the table in Datasheet View.

Now as we can see I’ve got three actors in here, fairly well known, although perhaps if you’re a young person you may not have heard of any of them. I don’t know. And these three actors I’ve got very basic information: first name, last name, year of birth. Now I want to add some more information about each of these so what I need to do is to add some fields to this table. And the first field that I want to add is each actor’s gender. Now I can do that either by using the space on the right here. So for instance if I thought, Well let’s just put M and F in for Male and Female. So Humphrey Bogart is M, Ingrid Bergman is F, John Wayne is M and automatically Access 2013 gives me a new field called Field 1 and then allows for another inserted field on the right here. So it’s very dynamic in terms of letting you add fields when you realize you need more information. Having added that data though if I go back into Design View, so click on Design View. I can see that it’s added my field. Now I don’t want it to be Field 1. I want to change the name to Gender. And the type I also want to change because I don’t want it to be short text. I only want there to be two possibilities here. I either want the gender to be M or F. So in the drop down at short text if I click on that, I want to change from short text to lookup. Now the idea of lookup is that I can look up potential values from a limited list of the possibilities. And in this case the limited list is just M or F. So let’s look at the Lookup Wizard.

So the first screen says, The Wizard creates a lookup field which displays a list of values you can choose from. How do you want your lookup field to get its values? You get a choice. I want the lookup field to get the values from another table or query or I will type in the values that I want. Now I’m going to type in the potential values. So I’m going to choose the second option and click on Next. What values do you want to see in your lookup field? Now if there is more than one piece of information to choose from in the sense that you need to show somebody say a number and an explanation you’d have more than one column. But for this we only need one column and the possible values, well the first value is going to be M and the second value, potential value, is going to be F. And they’re the only two values that people can choose from. So click on Next. What label would you like? Well the label of Gender would be fine. And then under that there’s a very important checkbox, Limit to list. Do you want to limit entries to the choices? Sometimes when you give people a choice of what to put in a field you say to them you could have this or this or this and in fact you could also type in your own choice if you wanted to. So you’re helping them by offering them a choice but you’re not limiting them to those choices that you’ve given them. Now in this case there are only two possible choices, an actor is M or F. So in this case we’re going to check Limit to list. The next question on that page we’re going to come back to later on. Do you want to store multiple? Well let’s just ignore that for the moment and click on Finish. Now what we get is a still a short text value but on the Lookup Tab in the grid at the bottom we’re told that the row source, the third entry here, lists the possible values that will appear as our choices. Now I’m going to save the changes we’ve made to this table and then show you how this works in Datasheet View. So let’s do a save and then let’s switch to Datasheet View.

Now watch what happens when I add another actor. Now having put in the basic information when I get to the Gender field now I get a control that is effectively what’s called a Combo box. And I have a drop down arrow to the right. If I click on the drop down arrow I see the choices M or F. I can’t type in anything other than M or F. I’m limited to this list. But I select M and that’s it and then I’m ready to move on to entering details of the next actor.

Now I want to do a similar thing to the movie table next so I’m going to close actor, save the changes, and I’m going to go into the Design of the movie table. And what I want to add here is the genre of the movie. So I’m going to add a new field called Genre and I’m going to say that it’s short text and I’m going to make it lookup as well. Now this time when I say Lookup Wizard I’m going to say I will type in the values that I want, click on Next. I’m still only going to only have one column but let me put as my genre options Comedy, Mystery, and say Thriller. Click on Next. I’m going to say Limit to list again but this time I’m going to say, Do you want to store multiple values for this lookup? And I’m going to say yes I do and then click on Finish. So save that and then go into Datasheet View to look at my data. Now let’s choose the first movie, Identity Thief, click in Genre, click on the drop down, and this time in this drop down I have my three entered options but I get checkboxes next to them and I can choose more than one option. And if I choose Comedy and Thriller and click on OK look what happens. I get Comedy and Thriller. Now one thing I should just point out, be looking at this a little bit more later on, the Datasheet View works like an Excel spreadsheet.

So if a column is not wide enough you can just drag it a bit wider, there we are. Now we can see that the genre for Identity Thief is Comedy, comma, Thriller. So I’ve got two values. Now if you know much about database design you’ll know that this is pretty bad design for a database and I’m going to change this a little bit later on. So if you’re looking at that and perhaps feeling a little bit horrified don’t worry too much about it because I am going to fix that later. But it does demonstrate something that can be useful, which is the ability to get multiple values in a field like the Genre field. And it’s a very straightforward way of doing it as well, as you can see. Now the number of possible values for the genre of a movie, it’s quite a long list but it’s a fine art list, maybe 10, 15, 20 different options, something like that. But supposing that I also wanted to put into my movie table a list of the actors in the movie.

Now let’s go back to the design of movie and let’s suppose I was going to put in here a list, Actors. How would I put the list of actors in there? Would I do it like Genre? Would I have first actor, comma, second actor, comma, third actor, comma, fourth actor? The answer to that is definitely No although many years ago some people would have designed databases like that. If you have a long list and particularly if you have a very variable list such as the actors in a movie or the crew in a movie or what about the special effects technicians? There are sometimes hundreds of them. You wouldn’t make a long list of them with commas in between their names. We need a different way of putting long lists of things together. The other alternative is to say have an Actor 1 field and an Actor 2 field and maybe an Actor 3 field. And with all of those you could put in the actor, the main actor, the headline, the second actor, so on. How many of those would you allow for? How many actors is the most that you might need? You can see that it’s not that easy to design a table where you’re not quite sure how many of something you need. Now what we’ve got so far in the movie table is very straightforward. We’ve only got one title and we’ve only got one year of release and the genre although there could be one or two or three or four of them there won’t be that many. But when it comes to actors or crew then you’re in a different situation altogether and as I say that’s what we’re going to look at in the next section.

Now in readiness for looking at relationships between tables in the next section I want to do one more thing in this section and that is to create a new table and the new table is going to be a genre table. Now we just setup a lookup using three possible genre values that I typed in. In reality it would be more efficient and more straightforward to have a separate table with all of the available genre values in it. And bear in mind that the genre values may change over time as well. If we have a separate table we can add new genre, remove ones that are no longer used, and so on. So it’s a pretty straightforward case as before with one interesting question. So I go to Create. I go to Create table. I can say create a new blank table or I can say create a new table design. Let’s go straight to table design this time and this time instead of getting the default ID I’m going to say well maybe I don’t actually need an ID; maybe I don’t need to number these. Maybe the genre values themselves could be the primary keys. I said earlier on that you’ll almost always use an ID but it’s only almost used. There are occasions when you don’t really need one. So I’m going to actually put in here Genre as the name of the field, the data type is going to be short text, and that’s all I’m going to have in that particular table. This will be the primary key. So click on that, click on there, it becomes the primary key. As such it must be unique.

Now I’m going to put in the genre values that I want. I know that when I switch to Datasheet View it will ask me to save what I’ve done. So click on Datasheet View. You must save the table. Click on Yes. Now I’m tempted to call the table Genre, but I’ve called the field Genre as well and what I’m going to put for the table name is going to look a little bit strange. Not everybody does this. I do but a lot of people that do quite a bit of database design do this. They prefix the names of the objects in their databases with a system of codes that remind you what something is. So the genre table I would call T-B-L-Genre, tblGenre. That reminds me whenever I see it that it is a table. And in fact I would normally use that same approach for those other two tables as well.

Okay so let me start putting in the genre values. First one is Action, the next one is Adult, the next one. I’ll carry on typing these, joining me again in just a moment.

So that’s my Genre table setup. I’m now going to close that table. I’m going to close the movie table. Save changes of course. And then I’m going to right click on Movie and click on Rename and that is going to begin with T-B-L as well and then I’m going to right click on Actor and do the same to that. And from now on I’m going to stick to this naming convention. When we come to things like forms and reports they’ll have similar prefixes to help me to identify things later on. It’s a very good thing to bear in mind if you are going to try and follow this naming convention that you don’t want to wait until much later on to set these names to these prefixed values. Use those names from the outset.

So that’s it for this section. I’ll see you in the next one.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!