From the course: Access 2016 Essential Training

Establishing relationships and maintaining referential integrity

From the course: Access 2016 Essential Training

Start my 1-month free trial

Establishing relationships and maintaining referential integrity

“

- Once our data tables have primary and foreign keys established, we need to let Access know which fields relate to which. We can do this with the Relationships tool, and typically, you can find that up here on the Database Tools tab of the ribbon, and you can find the option here for Relationships. Another way to find it is if you're currently working in a table, for instance I'll double-click on the Guests table to open it up, you'll notice you get two additional table tools here, these are the contextual tabs that open up when you're working with a table, and if we go into the Table tab here, you'll find another option to get into the Relationships. Either way, it starts up the same tool, however, in order to modify or create relationships between tables, those tables actually need to be closed, so even though you can get to the relationships through the Table tab, it'll often create a situation where you can't edit it once you get there, because the original table is still open. So let's go ahead and make sure that all of our tables are closed here, and then we'll go to Database Tools, and we'll click on Relationships here. The Relationships screen is essentially a blank canvas, where we can lay out a map of all of the different tables in our database and how they relate to one another. There's a new contextual tab here at the top, called Relationship Tools Design, and we get an option here with this Show Table window, to add the tables from our database into the relationships map back here in the background. If you close the Show Table window, then on the contextual tab here, you can get it back just by pressing this button, and you can also just right-click anywhere in the blank area and choose Show Table from the pop-up menu there. So, in the Show Table window, we can add in the different tables that are in our database. Notice we can also add queries here, or we have a listing of both tables and queries, now our database doesn't have any queries right now, so that's why this tab is blank. Let's go ahead and add in the two tables that are in our database. We could do that just by double-clicking on them, or you can select one and then press the Add button down here at the bottom. Once you've added both of the tables to the map back here in the background, go ahead and close the Show Table window. Now, each of these squares here represents one of the tables in our database, you can see we have the table here for the guest credit cards and the table for the guests. We can move these tables around here on the map, just by dragging by their title bar here on the top. And you can also resize them, so if you come down here to the corner and just click when you get this icon, you can drag the corner out so that I can see all of the different fields in that table at once. Now I'm going to go ahead and drag the guest credit cards over here onto the right side of guests, and then I'm going to put guests back over here, just to space them out a little bit. Typically, I like to read my relationships from left to right, so I'm going to say that the guests connect to the guest credit cards, rather than saying that the credit cards connect back to the guests, it just makes more sense in my head that the guest owns the credit card, so it should go left to right. But it doesn't really matter which order you do it in. In order to create the relationship, we just need to identify the fields that connect these two tables. In this case, I'm going to choose the primary key from the guest table, which is this ID field, and you can tell it's a primary key because of the yellow key icon, here. And it's going to connect to the field called Guest ID that's in the credit cards table, here. In order to connect them, all I need to do is drag and drop. So I will drag the ID number out of the guest table, and I'll bring it over here and drop it onto Guest ID in the guest credit cards table. When I do that, you'll get a new window that pops up, called Edit Relationships, and you'll see which fields that you dragged and dropped on top of, and if you need to make a change, for instance, if you just missed where you dropped it, you can use the dropdown list to refine your selection here. Inside of this window there's a checkbox that's actually a really important feature inside of Access, and it's this Enforce Referential Integrity checkbox, here. When I turn it on, we're going to be enforcing referential integrity, and what that means is that it'll create a situation where the database will not allow you to have a credit card for a guest that doesn't exist. So, in other words, if I move this out of the way, whenever we add a new guest, they get assigned an ID number. Whenever we add a credit card, we want to attach it to a specific guest ID using the same number over in this field, here. With Enforce Referential Integrity turned on, it won't allow us to enter in a new credit card and then enter in a guest ID that doesn't exist in the guest's table. It will also prevent me from deleting a guest, while accidentally leaving their credit card information in the database. In other words, it prevents what are called "orphaned records," records that don't relate to any parent information. This is a really great control to have on when it's appropriate, because it ensures that your database remains reliable. When we have Enforce Referential Integrity turned on, we've got two additional options here. We have Cascade Update and Cascade Delete. If I turn on Cascade Update, what that means is that if I make a change to a guest's ID number here, for instance, I just change it to a different number, then it's going to go through and find all the credit cards that are associated with that guest, and change the guest ID here as well, so that they stay related to one another. If I turn on Cascade Delete, that will create a situation where if I delete a guest here, it will find all of the credit cards that are associated with that guest, and delete them as well. I'm going to go ahead and leave both of those boxes off. When you have them both turned off, that creates a situation where you have to be very explicit about changes that you make in the guest table. For instance, if I have those checkboxes both turned off, it won't allow me to modify a guest's ID number or delete a guest if they have credit cards, so I'll have to be very specific about the changes that I want to make. So I'm going to leave it set up like this, with the Enforce Referential Integrity turned on, and the other two turned off, and we'll press the Create button, here. Notice that that joins the two tables with a line, and I get this notation here with a one over here, and an infinity symbol over here. That indicates that this is a one to many relationship, so we have a table situation where we can have each individual guest and have, potentially, dozens of credit cards stored in our system. If you need to make changes to the join between the two tables, all we need to do is double-click on the line, and you'll bring that dialogue box back up, and if you need to delete the join, just right-click on it and you can say delete from the menu there. Now, before I end this movie, I want to share one common tripping point here. The Relationships screen is actually quite large, and you'll notice that we have scroll bars over here on the right hand side and the very bottom of the screen. Sometimes what happens is that a user will accidentally click in the scroll bar region, and the relationships will just scroll right off the top of the screen. Sometimes you'll see this and think that your relationships have been deleted, so you'll go back and try recreating them again. In this case, all we need to do, really, is to just scroll back up to the very top of the relationships, and we'll find those back there again. So that's how you establish relationships between tables. Any time you create a new data table, you'll want to revisit the Relationships screen to tell Access how the new table integrates with the others.

Contents