Many-to-many Relationships in Access

hello and welcome back last time we created a relationship between the course ID in the course T table with the course in the student T table and we said that the relationship is one to many that means one course can be done by many students now what if a student chooses multiple courses this actually means that the relationship between course and students is not many to one that means course can be done by many students but also vice versa many courses can be done by one student so the relation is actually many to many now when you have a relationship like that it has to be broken down into two one-to-many relationships how is that done so the first thing we are going to do is to leave this relationship so I'm going to click on it right click and delete are you sure you want to permanently delete the selected relationship from your database yes so we delete that now we will close this do you want to save changes to the layout of the relationships yes now we will create another table and this table will be a link table all right before we do that we will double click on student and remove this field the course ID fields we will click here right click and delete this right at the bottom you may not see it here so I am going to click and delete it from here okay do you want to permanently delete the selected fields and all the data in the fields yes so that is deleted we will save this will not worry too much about these queries we can delete them later on so we will go to create we will go to table design and we will create a student course table so student course ID data type will be an auto number so I will just press a from the keyboard tab and then back here and now here I am going to write student ID and this will be a number and it will come from the student table press tab and next feel will be course ID before I do that let me just check in the course table how it is written so it is courses ID okay so courses ID tab this will also be a number and now our table is finished this is basically going to be a link table between the course table and the student table and it will help us to create true relationships and we will convert the many to many relationship between course and students to two one-to-many relationships so I'm going to right click on the table save I'll give this a name so I will call it student course T and okay so it's a no primarykey defined we'll say yes you define it for us and of course it's going to take the student course ID because this is an auto number so we'll close all these tables close all will go to our database tools go to relationships and here we have these two tables already and at present there is no relationship let me just reduce this a little bit here like this so that we can have some more tables here so I am going to click on show table so we will take the student course table add and close now we can build a relationship from this table to this table because we have the course ID so I'm just going to click this drag here and leave this and edit relationships window pops up and it shows you one-to-many relationship so we'll go to the enforce referential integrity and will say create so the link is created do you see that one too many similarly I can click here and drag this to the ID here and again we have a one-to-many relationship between the student table and the student course table I will enforce the referential integrity and create so we have created now a link table between these two tables because these two tables cannot be directly linked they have a many-to-many relationship now we will populate this table with some data all right now close the relationships tab Ian click here and it asks us do you want to save changes to the layout of relationships yes of course we'll click yes we will go to our student course table double click on it and we have no data here but we can take some data from the student tea table okay so we can say for example student 1 click see course 1 ok tab you saw that error appear because by mistake instead of writing 1 I wrote 10 and because we have enforce referential integrity the student ID 10 doesn't exist so you cannot add or change a record because the related record is required in table student e so we will say ok and we'll delete this now you have immediately seen the advantage of using enforce referential integrity so we'll go here we'll go to news let's say student 2 also takes course 1 student 3 student 4 takes course - student 5 takes course to certain 6 clicks course 1 1 and I can go on adding because I know the moment this number doesn't match the number in students ID table or students table this will give me an error see you get that error so you know you have reached the end of your table so we will just delete this record all right now we will save this Oh let us create a query all right so we will go to first of all let me close everything here close all so we will go to create query design and now I'll press the control key and select all the tables add clothes okay so I'll bring clothes here student here this is these are constraints when you are recording so I will just pull this grid here so that you can see the grid I will like earlier double click on the ID first name so this comes here if I click last name it will come here now I want to know which course these students are doing so I will click on the course name and that's it that's what I want I want to know this student is doing which course if I wanted we could delete this for example we could select this so you select this and just press the Delete key from the keyboard okay that's what I did for deleting the ID now let me run the query so I'll click on run here and there you see you get the results like earlier I will first save this query I will right-click on it save and we'll call this student course Q so the course student takes ok let me also now introduce you to the concept of the form so I click on the student table here go to create and click on Form here okay and you'll see that a form has been created the form structure has been created and you can see all the students are shown here so if I click on the last one here last record it will go to the last record and give us these details all the details of the student and also includes the student course let me just expand this little bit so student course ID is 8 and course the student has taken is 2 now let's say this student takes another course ok so we will click here press the tab key will go here and let's say I write one here ok all right and we will save this now so we will right click on this and say we will call it student form student F ok and now I'm just going to close this form and I'll go to our student course table put the student course table here double click on this and then you see entry has been made in the student course ID in the student course table number 10 and the student remains 8 and the course he has not opted for his 1 and earlier he had course 2 so if I now go to my queries let me see student course query here I'll just double click on this and you can see here that kamila Smith who is the number a student is properly represented this would not happen with the earlier single relation that we had between the student and B course alright so I can I don't even need to run the square it shows you here that it is 9 of 9 all right I hope you found this useful thanks for watching you

Channel: Dinesh Takyar

Views: 160,335

Rating: 4.6123347 out of 5

Keywords: link table in access, join table in ms access, forms in access, queries in access

Id: WtEj_Fb6eaw

Length: 13min 31sec (811 seconds)

Published: Wed Apr 24 2013