#How to Create Many to Many relationship in Microsoft Access #Composite Key#Primary Key#Relationship

[Music] hi this is karthik i welcome you all for this session in this session i'm going to teach you how to create many many-to-many relationship in microsoft access once again i repeat the topic how to create many-to-many relationship in microsoft access in order to achieve this task we should analysis table design first so here is a table design we are going to create three tables namely student course and course details so in the table student it is going to have two fields namely sid and s name where sadie is going to be a short text and primary key and s name is going to be a long text and x followed by the table course in which it is going to have two field names namely cid short text and primary key cname is going to be a long text followed by the table course detail in which it is going to have two fields namely sid and cid both are going to be the short text and primary key so combination of this keys are called as composite key i will explain you the later about the composite key so now moving on to relationship what is the relationship why we should have a relationship if you have a relationship you are allowed to split and store the data in different tables and followed by the primary key what is the primary key is used to provide a unique value or used to avoid with duplicate value or used to avoid the null values next followed by composite key what is a composite key a composite key is a composition or a combination of one or more column used to uniquely identify each records or rows and next followed by many too many so this type of relationship use a link or a join table in order to connect the other two tables for example the student and course can be connected by a course detail this is called as many-to-many relationship finally we are going to create the relationship design like this where we are going to have three tables namely students course and course details where the course dds is going to have many too many relationship with the team course and student so in order to achieve the task we should open access first how to open access it's quite simple go to start and look for the alphabet e and a where you can see axis once again i repeat these steps go to start and look for the alphabet e and click on the access icon when you click on the access icon it will takes you to a view called as backstage view where you can see two types of databases namely blank desktop database and custom web app so today we are going to use bank desktop database when you click on blank desktop database a pop-up window will be generated where you want to fill up with the file name let us say db okay underscore college let us say one any name it's a user defined name and then if you wish to change the location of your database allowed to do so by clicking this folder icon and select the location where you wish to save your database so today i'm going to use the default location and i'm going to click on create when you click on create it will takes you to an ide where you can see table one by default so now next step is we want to design the table how to design the table just right click and navigate to design view so when you click on design view it lasts for the table name just fill the table name as student and click ok now followed by the field name is going to be a yes underscore id it is going to be a short text and then followed by s underscore name it's going to be a long text and then save it close it next we want to create one more triple how to create one more table just go to create where you can see tab icon when you click on table icon it will generate a default table table one or else you can directly navigate to table design view by clicking this icon table design so now you're in design view so now next table is see underscore id it is going to be a short text and c underscore name it is going to be a long text and then we want to insert the primary key to c id so select this row and right click and insert the primary key just save it when you click on the save icon it will ask for the table name let us say course and click ok and then close the table now we want to create the third table so how to create the third table just go to create and again you can use table or table design so i'm going to use table design so first save it so let us say course details let us say course detail click ok and then followed by yes underscore id it is going to be in short text then followed by c underscore id it's going to be a short text and now we want to insert the primary key to both cid and sat how to do so so just click the control button in your keyboard and select the two rows and then right click insert primary key once again i repeat the steps just press the control button on your keyboard and then select the two rows by using the mouse pointer and then right click and insert primary key now save it click ok and close it now we have created three tables our next task is we want to create the relationship how to create the relationship if you wish to create the relationship navigate to database tools where you can see relationship icon so once again i repeat the steps navigate to database tools and click on relationship icon in order to create the relationship when you click on the relationship icon it will generate a pop-up window where the list of tables will be displayed right here so you want to select the tables in which you want to create the relationship today we are going to use all the three tables so i am going to select all the three tables click add now close it now just arrange it let us say student let it be here let it be course here so now what we are going to do is we are going to create the relationship how so here you can see edit relationship icon just click on this icon when you click on this icon it will takes up to a pop-up window edit relationship where you want to click create new so and you want to fill the left table name write table name left column name and white column name so left table name should be student right table name should be state edge left column name will be sid and here s id click ok so where you can see the relationship type is going to be one too many and then this is a very important you want to check enforce referential integrity and then click on create now you can see you are created one-to-many relationship between the table student and course detail again we want to create one more relationship between the table codes in course details so how to do so again go to edit relationship icon and then click on create new and then left table name will be let's say our course and white table name will be the course details and let us say cid and this one is in cid click ok now you can see it's a one too many relationship and check the end force referential integrity and click create in order to create relationship now you can see you have created the relationship that is one too many relationship between the table course and post detail now the course detail is said to be many to many relationship with the table student and course and save the relationship by clicking the save icon and close it now you want to enter the value just double click the table in order to enter the values click on the table let us say course 101 let us say uh ug let us say course one or two let us say pg let us say course one or three let us say phd and then save it save and process and next one let's double click on the table student and let us say s will not one use any then s102 use any name and s103 use any name and then save it so where you can see a small plus button right here just expand it and fill let us say c101 and let's say c 1 r 2 and let us say c 1 or 3 because of a relationship we want to fill this columns and just save it and close it now when you double click the table close detail where you can see the sid and c80 are already filled why how this happened because of the relationship so i think you guys have enjoyed this video thanks for watching this video

Channel: Karthik Dakshinamurthi

Views: 1,297

Rating: 5 out of 5

Keywords: #Many to Many Relationship, #Composite Key, #Primary Key, #One to One Relationship, # Many to Many Relationship using Three Tables, #Microsoft Office, #Microsoft Access, #Access Table and Relationship, #Access one to Many and Many to Many Relationship, #How to create Tables and Relationship in MS Access, #MS Access Table, #Database Relationship, #Multiple Primary Key, #Relationship, #Table and Table Design in Microsoft Access, #How to insert primary key in Microsoft Access

Id: UtyoAeKEZ0w

Length: 10min 34sec (634 seconds)

Published: Sat Aug 08 2020