Get my free 3 hour Introduction to Access 2013 course. Get 17 training videos to help you learn MS Access 2013. Click here to get the free course: simonsezit.leadpages.net/free-access-2013-course/
In this video tutorial, learn the basic steps on how to create relationships between tables in Access 2013.
Get the full course on Access 2013 course here: www.simonsezit.com/courses/microsoft/learn-microsoft-access-2013-training-tutorials/
View the entire Access 2013 video playlist: www.youtube.com/playlist
Stay in touch:
The Simon Sez IT email newsletter: bit.ly/18bMwY0
YouTube Channel: bit.ly/foiItB
If you enjoyed the video, please give a "thumbs up" and subscribe to the channel ;-)
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 dialog 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. 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.
Sorry, we couldn't fit the entire video transcription here since YouTube only allows 5000 characters.