creating relationships in access This is a topic that many people are looking for. khurak.net is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, khurak.net would like to introduce to you Access 2016 – Relationships – How To Create One To Many Relationship in Database Between Two Tables. Following along are instructions in the video below:
“And welcome. Students today s tutorial will be in microsoft access. 2016. And i ll ll be showing you how to create table relationships in this example.
I m going 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 ” ..
Thank you for watching all the articles on the topic Access 2016 – Relationships – How To Create One To Many Relationship in Database Between Two Tables. All shares of khurak.net are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.