Understanding Access 2013 Relationships between Tables – Part 1

Database management is not all about storing your data in the form of tables; it is also about correlating data, ensuring data consistency, and enforcing referential integrity. A great way of achieving this is through table relationships in Access 2013. By using a foreign key or a common field, you can link two or more tables. This helps in reducing data redundancy, inconsistency and errors. In Access 2013, relationships can be defined just by dragging fields on top of the fields. The relationships are very well illustrated in the form of diagrams in the design view.

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

Do you need to learn Access 2013? Get our 9-hour of Microsoft Access 2013 training – click here.

Video transcripts:

Welcome back to our course on Access 2013. In this section we’re going to look at relationships between tables in an Access 2013 database and we’re going to begin by looking at genre for a movie.

Now first of all I want to go to the movie table and I want to open it. So we’re going to look at the data. So double click to open. And you may remember that for one movie, Identity Thief, we earlier entered the genre as comedy, comma, thriller. And you may also recall that we can have a number of different genre defined here separated by commas. But as I pointed out that’s not really good design and it carries with it a number of problems that will become apparent a bit later on. So what we’re going to do is to setup a proper relationship between the movie table and the genre table.

But for the moment let’s now go into the table design for movies. So I go into Design View. We had a field there, Genre, which is the one that holds currently the genre values separated by commas. I want to delete that row. It’s straightforward enough. When I’m in Design View the contextual tab Design in table tools is available and one of the options there is delete rows. So I can just literally delete a field. Now note the warning you get if you try to delete a field in a table. Do you want to permanently delete the selected fields and all the data in the fields? When I’m deleting this I’m deleting any data for any record, any movie that’s got genre information. Now at the moment I’m happy with that because I only put that genre info in as a demonstration so I’m going to say yes and the genre information is gone. I’m going to close the movie table again and say yes to saving changes.

Now what I’m going to do is to go to the Database Tools Tab and on there I’m going to click on Relationships and relationships gives me a diagrammatic representation of the relationships between the tables in my database. Now at the moment the diagram there is completely blank. It’s behind this little dialogue but it’s completely blank. And I want to add two tables to it. I want to add the movie table. So select it, click on Add. And I want to add the genre table. Select it, click on Add.

Now what I’m going to be doing is to create another table that links movies with their genre values. So for one movie there will be multiple genre values. Now the way I do this is first of all I will do Create table. So let me go to Create table and I’m going to go into Design View. I’m going to call the table tblMovieGenre just to emphasize the fact that it basically links a movie to a genre or many genre. By default an ID field is created. But I also want two more fields in it. One field I want is a field that will point to the movie that I am referring to. So I’m going to put in here movie. The next field will point to the genre. And basically what will happen is that in this table I will have many entries that link the movies to their various genre.

So first of all let me save this table, the newly defined table. Let me go back to relationships by clicking on its tab here. When relationships is selected I can go back to relationship tools, the Design Tab, and one of the options there is Show table because I now want to show an additional table. And the additional table is the new one, MovieGenre. I’m going to add that, close. I’m just going to pull it down between these two. And the way that this works is this acts as a sort of link between movies and genre. And the way the link works is actually fairly straightforward but it takes a little bit of getting used to. The way it works is that in the linking record, the record in tblMovieGenre we have a link to the primary key in the movie table which is the ID. And the key here, the movie value, is what’s called a foreign key into the movie table. Similarly the genre, the value here, is a foreign key into the genre table primary key.

Now if you’ve been paying really close attention you may see a slight problem here but it’s a very regular kind of problem and we’re going to have to face it sometime so let’s face it now. If you look at the genre table, let me just open the genre table up again in Design View, its primary key, genre, is a short text field. If we go into the Datasheet View we’ll see that the sort of thing we’ve got there, they’re words like Adult, Adventure, Animation, that’s fine. Let’s close that. This field, Genre, in the table MovieGenre, the equivalent, the one that’s going to be the foreign key is also short text. So that’s absolutely fine. If I look at the movie field in the MovieGenre table, the linking table, that’s short text. But if I go into the movie table, so let me open that in Design View, the ID there is not short text. It’s something called AutoNumber. And these AutoNumber values, the ones where it automatically gives it a number when you add a record are actually what are called long integers. So they’re a special kind of number called a long integer. And in fact in my linking table, my MovieGenre table the data types must match. So the movie here in order to link up must not be a data type of short text. It must be a data type of long integer. Now the way we do a data type of long integer is to select number and then just make sure that it says long integer down there. Now that may seem a little bit strange at this stage if you’ve not seen this before, but most of the time that you’re setting up relationships you’ll be setting up relationships to AutoNumbered fields and therefore making the data type of a foreign key into a long integer will be something that you do a lot. So once you’ve got your head around this the first time it actually almost becomes routine to do it from that point onwards.

So having changed the data type of movie to number and specifically long integer I’m going to close the MovieGenre table and save its changes. I’m also going to close the movie table. No changes there anyway. And let’s now look at relationships again.

Now what I can do in order to establish these relationships using keyboard and mouse is to literally drag fields on to fields. Now I know that the movie table ID, this field here, let me just click it to select it, corresponds to movie in the MovieGenre table. So all I do is grab the ID, pull it over here, and drop it on to movie. And what happens is that Access 2013 creates a relationship. And what I do in this dialogue is to define some of the properties of that relationship. Now perhaps the most important of those properties at this stage is the one that is set using this checkbox, Enforce referential integrity. Now you may or may not have heard of referential integrity before but what it basically means is that if you have something like a movie that has various properties such as the genre it belongs to or the actors who acted in it or the crew that were involved in making the movie and you’re setting up all these kind of relationships that we’re looking at here. It means that you cannot delete, for example, that movie without clearing up all of these relationships as well. So you can’t leave odd bits of data laying around that don’t have a movie that they belong to anymore. Now you will almost always enforce referential integrity and when you do there are another couple of options here. I’m not going to look at those at the moment. We’ll come back to those later. But basically once you’ve said you want to enforce referential integrity you can create the link between those tables. Now there are a couple of other options. There’s a join type that again we’ll come back to later on, but for now let’s just say Create.

And what we finish up with is a one, note the one there, to many, note the infinity symbol; a one to many link which means for each one movie there can be many movie genre records, which means one movie could have three or four or five or six movie genre records. It might have none of course. And each of those will specify one genre that applies to this movie. So that’s half of the job done. Now let’s do the other half.

Let us say we click on the genre in the genre table and we drag on to the genre in the MovieGenre table. Again we’re going to enforce referential integrity, click on Create, and we have the other half of the link. And what this says is that for each genre there can be many movie genre records because of course if the genre is comedy there are many comedy movies. And in that way we have created all of the linkage we need to create the relationship between movies and genre.

Now what you have there is an example of quite a tricky concept in database design but it’s an absolutely fundamental and essential concept to understand because it is at the basis of the design of all relational databases nowadays.

Now I want to just help a little bit on this by going back and putting in some data to explain a little bit more about how this works. So let’s close the relationships diagram. We’re going to say yes to saving the changes to the layout because we’ll be coming back to the diagram again later. And let’s go back into the table MovieGenre, let’s open that up, and what we would normally do now is to type in a movie number and a genre. So let’s go for one of the movies. Let’s open the movie table. What about Independence Day? That’s by the way that columns not quite wide enough so let’s make that wide enough. Independence Day is movie number six. So let’s go into MovieGenre. Let’s put in movie number six and one of the genre is Action. Let’s go to the next one and let’s put in Adventure but let’s spell it wrongly. There we are. It’s spelled wrongly. You cannot add or change a record because a related record is required in the table genre. It will only let me add legitimate values for genre here and that’s one of the very important aspects of applying these relationships. You make sure that your database is consistent with itself. So if I now spell that correctly, Adventure, that’ll be much better. So let me just go through and quickly put in the rest of these for that movie. So Action, Adventure, Sci-Fi, and the final official one for Independence Day is Thriller.

And of course I could go through the other movies that I’ve currently got in this database and put in the same sort of information. But typing it in this way is a little bit laborious and there is a better way of doing it and we’re going to come back to that later when we look at forms. But for now let’s turn our attention in the next section to a different sort of relationship and this time we’re going to look at adding actor information to our movies. So please join me for that.

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!