[AUDIO LOGO] INSTRUCTOR: In this section of the course, we're going to shift our focus and start talking about relationships, because relationships are one of the fundamental concepts that you need to get your head around when you're working with tables in Access. Now, relationships are really about linking different tables together. 

So currently at the moment, we only have one table in our database, but that's going to change in a moment. We're going to add some more tables. I'm going to show you how to link them together and why you might want to do that. So let's take a look at that latter point, first of all, and talk a little bit about exactly what a relationship is. 

Now, for example, I've got two separate tables just here. Let's say that the one on the left is the ticket table and the one on the right is the address table. Now, these two tables are individual tables that are not linked together in any way, shape, or form at the moment. Now, let's say that the table on the left is the main table in our database. And in the real world, this would have a lot more columns than just three. 

Now I have the customer information in that table on the left. And maybe once I've built my database, I want to be able to interrogate and maybe run queries and reports that utilize the customer's address. But currently I don't have that addressing information within that main table. I do, however, have the address information in a completely separate table that we can see on the right hand side. But because these two tables aren't linked, they don't interact with each other. 

So currently, if I was to search for the customer, and I wanted to see the address information, it's not going to be able to find that because the database doesn't really know that these two tables are linked together. So the way that we would link these two tables is by using the common field. And you can see the common field here is highlighted. It is the customer field. So I can link the table on the left to the table on the right using the customer field in both because it's common between the two tables. 

Once we've done that, we then have that link and it means that we can access the address information in the other table. Now what if we have a slightly different situation? Maybe we have two tables, but they don't have a common field. So once again, I have my table on the left, which has ID, ticket number, and customer. We have address information in the table on the right, but there's no field that they both share to link these together. So how would we get around that? 

Well, this is where we would create ourselves a link table. And a link table is a table that kind of sits in between and contains a field from one table and a field from another. And we can then use that link table to bring those tables together. So you can see that example here. The link table in the middle has two fields. It has customer, and it has address. And we have the customer field in the ticket table and the address field in the address table. 

So using that link table in the middle, we can link both of these tables together. And that's basically how relationships work in Access. And we're going to be exploring the different options we have, not only for linking tables together, but also creating link tables to link together two tables that don't share a common field. So I hope that makes things a little clearer. I will see you in the next lesson. 

So now we understand how relationships work, it's time to put that knowledge into action. Now, currently we've been working basically in one table, that is Turbo Ticket. And we have quite a few columns in this table, but maybe I want to create another table that houses some additional information. So something that I don't really have in this table currently is a status field that indicates if this call is active, if it's on hold, or if it's closed. 

So what we're going to do is we're going to create another table that holds all of these different statuses. So let's go up to the Create tab. We're going to go to Tables and we're going to choose Table Design. Now, remember, when you choose Table Design, you don't get an ID field. That's OK. In this case, we're just going to have one field and it's going to be called Status. The data type is going to be Short Text. And if we take a look at the field properties, I'm going to take this down to 50. 

Is it required? Let's say, yes. Are we going to allow zero length? No. Is this indexed? Well, yes, I could very well search on all calls that are closed, for example. Now, the final thing we need to do here is we need to set this field as the primary key field because it's the only field that we have in the table. We don't have much of a choice. So let's select it. And just above on the table design ribbon, we're going to click Primary Key. 

So now that we've done that, let's right click and switch into datasheet view. We're going to be asked to save our table, which is fine, and we're going to call this Table Status and click on OK. So now I have my table. It's a very small table. it just has one column, one field called status. So now I can add into this table the different statuses that a call might have. 

So a call might be active, it might be on hold, it might be resolved, or it might be closed. So now we have two active tables. Now I'm going to close down both of these. And we're going to jump across and take a look at the table relationships. So to do this, we jump up to Database Tools. We have a relationships group and we can jump into the Relationships window. 

Now, currently this is empty, but if we cast our eyes over to the right hand side, notice we have an Add Tables pane. And just below I can see both of my tables. So I'm going to select both of them and we can hold down Control to select more than one and click on Add Selected Tables. 

And this adds the tables to the main window. We can drag and drop them if we need to reorganize them. We can also resize them. If we want to see all of the fields, we can drag that down, and we can really place these and give ourselves a bit more of an idea as to how these tables relate to one another. 

Now currently these tables are not related at all. So if I wanted to run a query or search for a call by its status, I couldn't currently do that because our status table isn't related or linked to anything in our main table. And just so there is some database terminology for these different tables. The main table, which is normally the biggest table that has the majority of your fields in it. So for me, that's Table Ticket. This is your fact table. 

And all of the smaller tables that hold additional information, these are called dimension tables. Now somehow, I need to link Table Ticket to Table Status. And if you recall in the previous lesson, we were talking about linking tables using common fields. And these two tables don't have a common field because we don't have a status field in the Ticket Table. 

So we're going to need to create ourselves another table. It's going to be a link table that contains a field from Table Ticket and a field from Table Status. And then we're going to be able to link these tables together. And that is exactly what we're going to do in the next lesson. 

- If you're not a subscriber, click down below to subscribe so you get notified about similar videos we upload. To see the full course that this video came from, click over there and click over there to see more videos from Simon Says it.