Site Loader
Microsoft Access 2013 Tutorial – Relationships Between Tables – Part 1 – Access 2013 Training


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. 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. 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. 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. What happens is that Access 2013 creates a
relationship and what I do in this dialog 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 lying
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. 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. 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. 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. 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. 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. 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.

Reynold King

55 Replies to “Microsoft Access 2013 Tutorial – Relationships Between Tables – Part 1 – Access 2013 Training”

  1. what happens when you don't want to refer to each record by its autonumberd value? so in this case enter in "independence day" instead of "6". The problem arises when you have multiple titles of the same name, like true grit, but how do you get around this. Thanks very much

  2. Great tutorial! Helped me to understand this concept more clearly. I'm trying to create a relational database using one table containing company names and addresses and another containing employees of each company and their current set of qualifications (relevant to the training organisation I'm working for). Slightly more complex but I'm getting there. Thanks for this!

  3. Hope you can answer a quick question for me.  With the new Access, I want to make a form/app with a data base; basically I am inputting market data selling and buying amounts and then it will compare all and tell me the best profits and where to trade them.  I want to use access over excel because I want a nice gui that asks for the info and a nice place to input it. This is for a computer game.  Now my question is After I create this.  Is there a way to save it or convert it so others (friends etc.)  can use it without having access?  I thought I had read somewhere that access creations can now basically work like an app.   This is my first time using access in about 15 or so years so wasn't sure about how it's working now.  Thanks.

  4. There are a dozen ways to do anything in Access.  Doing it right, with the best practice resulting in data structures that remain relevant is my objective.
    Two observations and questions follow:
    1) I have not seen a many to many created with data, are there disadvantages?
     2) It seems one movie could be directly connected to any number of genres with two tables, using the genre table as a record source (rather than the additional junction table).  Is a many to many required here, what would be the other options (aside from a lookup)?

    Also, at the time of my comment, there were 13 dislikes, could those of you who disliked this, elaborate on what you found to be wrong with this tutorial, rather than just giving a thumbs down?  Thanks.

  5. You are the slowest speaker ever and I hate your tempo its so frustrating this 15 min video could have been shown in 4 min 

  6. Simon…thank you for a great presentation! I have been working in 2013 for a week now, and have viewed several of your videos, but my system does not seem to want to provide the crows foot depiction in my relationships. I seem to just have lines without any icons? Any thoughts? Thanks!

  7. @ Dixie Peach, make sure you have a primary key designated for each table in the relationship.  In my experience, if one of the fields is not selected as the primary key (it doesn't have to be one of the fields involved in the relationship), then the 1 to 1, 1 to many, or many to 1 icons won't show up.

  8. why add the tblMovieGenre at all?
    why not just do a 1 to many relationship directly between tblMovie and tblGenre?

  9. I saw no reply to this question, so I will ask again…  Why add another table called moviegenre?  Why not have a direct relationship between the movie table and the genre table?  It looks like an unnecessary extra step that clutters up the database.

  10. A.O.A i want to make relationship between 5 entites……………..
    plzzzzz any body help me……
    kis topic ya system par me database banoun????

  11. I'm crying. Literally. I am in college. Sitting in a dining hall by myself. In a corner. Crying laughing.

  12. Good morning, Please I am trying to write a code to validate the data entries to avoid duplicates on a fault log database. it works for the stLinkCriteria which is a string datatype (short text) but not for stDCriteria which is a date data type… it keeps throwing an error "type mismatch" or "datatype mismatch" on this line (stDCriteria = "[datelogged] = #" & DateTime & "#".

    Although the data type for datelogged is Date/Time in the database…

    I humbly await your help.. thank you

    Private Sub Form_AfterUpdate()
    Dim NewTerminal As String
    Dim stLinkCriteria As String
    Dim DateTime As Date
    Dim stDCriteria As Date

    NewTerminal = Me.cboTerID.Value
    DateTime = Me.txtDateLogged.Value
    stLinkCriteria = "[serialptrid] = " & NewTerminal & ""
    stDCriteria = "[datelogged] = #" & DateTime & "#"
    If Me.SerialptrID = DLookup("[serialptrid]", "Fault_Log", stLinkCriteria) Then
    If Me.DateLogged = DLookup("[datelogged]", "Fault_Log", stDCriteria) Then
    MsgBox "This terminal " & NewTerminal & ", " & DateTime & ", has already been entered in this database." _
    & vbCr & vbCr & "Please check terminal selected", vbInformation, "Duplicate information"
    Me.Undo
    End If
    End If
    End Sub

  13. You are very slow in explaining and doing things, man. You are putting me to sleep. Please speed it up a little.

  14. everyone's complaining, but i needed it to be slow and meticulous, otherwise I would have never understood. thanks.

  15. Thanks. I've watched several videos today trying to understand how to create relationships. Your video is the only one that has given me the understanding. thanks. keep it up.

  16. I think you are a very good teacher… Love your work. 
    I'm trying to figure out an issue in Access and i really really hope you will assist me. If I'm selling cars for individuals and each individual might have one or more vehicles at the price they wish to sell for, how would i setup the database to reflect the customer's info (persons I'm selling cars for) and their vehicle details with their desired selling cost bearing in mind a customer could be selling more than one vehicles.
    I setup a customer table with all the customers contact info but I'm thinking i would need to setup individual vehicle table for each customer because even though two customers might  be selling the same exact type of vehicle, same year and everything the selling cost will differ a lot of time. So you see the regular one to many relationship won't work for this system or can it? Not sure how to work this one at all. Will you help

  17. So how would I write a query that looked for TWO movie genres, say "Action" AND "Adventure"? Meaning to say, how would I write a compound criteria within the SAME field. I thought all I had to do was create the query and then type "1" AND "2" in the Genre fields criteria box. When I do that, I get NO results. However, when I enter either a 1 or a 2 in the Genre field's criteria box, I get a hit.

  18. I have a question, why not ditch the data type auto number for the Movie field and make it a Short Text ? this way is more realistic and you link to the movie foreign key attribute in the join table.

  19. Thanks for the helpful video. And I've a question for what sort of Laptop should I buy to work with Access, Excell and all the programs and window 7,10 or which one. For these days there are so many different kinds. So please, like to have your suggestion. Thanks.
    Jack.

  20. hi im hoping you can help me. ive created 2 tables on my database on ms access. one is 'customer details' etc and one is 'completed' sales/deals. what i want to do is keep all records of customers for cars in the main big 'customer details' table and any completed deals from that table automatically added to the 'completed' table. i want to have a button or option like completed on the end of the table maybe like a yes or no column. by which i could click and it would automatically add the relevant fields i want to the completed table. i would like to know how i can go about that.

  21. The magic of relationships between tables has eluded me for a long time. After watching this video I have slightly improved sense of this voodoo.

  22. Simple and nicely explained. But the repeatedly wrongly pronounced "genre" is a little annoying. It is ʒɑ̃ʁ, not ʒɔ̃ʁ…. please!

Leave a Reply

Your email address will not be published. Required fields are marked *