Access 2016 - Relationships - How To Create One To Many Relationship in Database Between Two Tables

hi and welcome students today's tutorial will be in Microsoft Access 2016 and I'll be showing you how to create table relationships in this example I'm going to show you how to create a one-to-many relationship between two tables let's go ahead and get started so we see that I have my database open right here and I have two tables this first one is called lab supplies let's take a look at the fields in this table I double click it to open it up in this example we are running a lab and we have all of these different supplies needed in our lab one of the columns is called vendor ID this is the column that I want you to focus on for this example the vendor ID column contains all of the vendors that sell us the labs that we are the lab supplies that we use so we see that one vendor like v100 here can sell us multiple supplies you will see it be it'll be repeated throughout several records within our table right here alright so there's a little bit about the lab supplies table let's close that and let's open up vendors and so the vendors again are the vendors that sell us the lab supplies so we have their contact information all their postal code email and all that stuff but notice right over here I'm along the left side we have a column called vendor ID these vendor IDs right here these are the vendor IDs that sell us the lab supplies right so we looked at v100 earlier and we said oh they sold us some other supplies so that looks good so now that we've focused on this vendor ID call them and we've established that it's in both tables and they basically reference each other let's go ahead and show you how to create a relationship between the two tables to where if it updates in one table it'll automatically update another so I'm going to close the vendors table now it's time to create the relationship so to create a relationship we're gonna go up here to the database tools tab database tools so you click on database tools and it's gonna open up all these different buttons here now there's a group right here called relationships and there's a button right here called relationships so let's go ahead and click on that so when you click on relationships the show table dialog box will appear now sometimes it won't show up automatically if it doesn't show up let's say mine didn't show up so I'm going to close it real fast and I clicked relationships and it's not I could go right up here to the relationships tools design tab which should be open by default go to the relationships group and click this button right here show table and there it is all right so we have the show table dialog box back up now I'm going to double click on my two tables alright so I have my two tables here and now I'm going to close this show table dialog box and now what I have to do is I'm going to open up or I have each of my two tables listed here and notice that if I click and drag from the top I can kind of move my tables around so I'm gonna move my tables a little bit further from each other so that I can basically see all of the fields within the table but notice that each one still allows me to scroll I'd rather see all of the fields at once so if I go to the bottom right at the table you'll see the icon will change to a sizing handle I'm gonna increase the size of the table here all right so we've got lab supplies and we've got vendors okay well if we think about this the vendor ID is on both of these right here and so if I have vendors and I have vendor ID under vendors and I have my lab supplies and I have vendor ID under lab supplies what I could do is create a relationship between those two fields so I'm gonna just click and drag from vendors over to vendor ID right here vendors or sorry vendor ID to vendor ID so notice that when I click and drag on vendor ID and I start to drag it gives me a no symbol until I get over to the other table and then I go right here to vendor ID now notice I'm at the point of the cursor at the very tip of the cursor and then I release and then it says edit relationships and this edit relationships dialog box appears and I have vendor ID and vendor ID right here one says vendors one says lab supplies all right now I'm gonna click on this right here which is enforce referential integrity and then I'm going to click both cascade options and I'll show you what these do in just a little bit but basically I click on those three things right there it says vendor ID in both columns and then I click create alright now you'll notice two symbols are created here I'm gonna switch up the sides of my tables and notice that the relationship follows I'm going to switch them just to make easier to understand now this has one right next to vendor ID and then this down here says an infinity symbol so what is it actually saying it's saying one vendor one can provide many lab supplies lab supplies one vendor can provide many lab supplies so what this is called is a one-to-many relationship again a one-to-many relationship so you say what does it actually do well I'm gonna go ahead and click close right here it'll ask me if I want to save the changes to the layout of the relationships since that's what I do want to do I click yes and then what it actually did was well let's see what it did if I open up the lab supplies table we see that it looks pretty much the same everything looks pretty much the same here we still have v100 up here alright so we close that one and then we open up the vendors table we say oh that looks pretty much the same - we still got our vendor ID but look at this there's this little tiny plus sign to the left of the vendor ID column all right well let's see what that does if I click this plus sign on v100 we now see all four of the items that v100 supplies so this is really nice because what I can do now is if I quickly want to see Oh what does v100 supply us I could go right there and it gives me the four items that v100 supplies from the lab supplies tables we say oh that's fantastic now we have an easy way from one table to basically look at both information just by clicking down on this option right here now when we enforce referential integrity that means that one reference if we reference it on one it'll reference on the other and then the Cascade options are right here so when we click the Cascade options that allows it to cascade down so now we get to a point where we say alright that's awesome but then we notice it says V zero zero two of is zero zero three all the way down to nine and then it goes to 100 well this one was probably supposed to be called V zero zero one so I'm going to get rid of that 100 and type zero zero one and then I'll press tab or enter now check this out I changed V zero zero one here that's the vendor of this this Parker core solutions well I changed it here and I say alright that's good I'm gonna go ahead and close this table now one of the great things about enforcing referential integrity and creating relationships well is now that when I open up lab supplies check it out this one right here that used to be v 100 now says V zero zero one so now I only have to update the database on one table as opposed to going through and finding every single vendor ID within this lab supplies table and changing it from V 100 to V zero zero one since the relationship was already established between the two I can change it in and the one-to-many table which is the vendors table then one that contains the one and since I changed it in the vendors table it automatically got updated in the lab supplies table so hopefully this tutorial has helped to explain a little bit about one-to-many relationships and how to adjust your relationships in Microsoft Access and help your database to communicate within itself so if this video has helped you please take a look at my other Microsoft Access videos please consider subscribing to the channel and if you have a comment or video requests please put it in the comment section below thank you so much for watching and have a great rest of your day

Channel: Professor Adam Morgan

Views: 257,498

Rating: 4.9137988 out of 5

Keywords: professor adam morgan, access 2016 relationships, how to create a one to many relationship in access 2016, how to create a relationship between two tables in access, how to create one to many relationship in access 2013, how to set up table relationships in microsoft access 2016, creating a one to many relationship in a access 2013 college database, to, access, relationship, in, create, one, 2016, how, many, relationships, between, two, tables

Id: NtRAyS0LLlk

Length: 7min 57sec (477 seconds)

Published: Fri Nov 17 2017