#How to Create One to One relationship in Microsoft Access #One to One Relationship using 3 Tables.

[Music] hi this is karthik i welcome you all for this session in this session i'm going to teach you how to create one-to-one relationship in microsoft access once i can repeat the topic how to create one-to-one relationship in microsoft access in order to create one-to-one relationship we should analysis the table design first so first we want to create three tables namely student subject and fees in the table student we have the field names like student id it will be of short text and primary key followed by student name it will be of long text and next the table subject the table subject we have sid followed by short text and primary key and physics chemistry math and english followed by yes or no data type and third table is fees the field names are sid it will be of short text and primary key and followed by pages so all these three tables where you can see a common field name is there s id s id and s id of same data type short text in primary key by using this field name only we are going to create a relationship so now next question is what is the relationship why we need relationship if you have a relationship between the tables you are allowed to split and store the data in different tables if you have the relationship you are allowed to split and store the data in different tables so what is the primary key it is used to provide a unique value or used to avoid duplicate values so what is one to one relationship so it allows only one entry in all the tables so now our next task is we want to open microsoft access how to open microsoft access it's quite simple go to start and look for the alphabet a so in here where you can see access once again i repeat these steps go to start and then look for the alphabet a under a where you can see access just click the icon in order to open it when you click on the access icon it will takes you to a view quartus backstage view where you can see two types of databases namely blank desktop database and custom way back so today we are going to use blank desktop database just click on blank desktop database when you click on blank desktop database it will generate and pop up window where you want to give the file name and if you wish to change the location of your database just click this icon in order to do so and select the location where you wish to save your database and click ok today i'm going to use the default location and i'm going to name it as db stu okay let us say one and click on create in order to create the database when you click on create by default it will generate a table called as table one so we want to navigate to design how to navigate to design view so just right click on the table and go to design view or else where you can see view just click on this and then go to design view when you click on design view it will ask for the table name so what is the first table name it will be student and click ok so now you are in design view you want to design the field names what are the field name smds for id short text primary key and s underscore name long text so yes underscore id it will be of short text and primary key so here you can see key it is called as primary key if you wish to remove the primary key just right click here and click on the primary key in order to remove it or if you wish to add just right click and click on primary key to include it and followed by s underscore name it will be of long text now we have completed our design so now just close this and it will ask to say do you want to save changes to the design of the table student pick yes in order to save it now you want to enter the values so how to enter the values just double click the student table in order to enter the values so let's say s101 and name is a user defined name you can give any name as you like and there's one or two any name as you like and s one or three any name as you like and s or not for any name as you like okay now you filled up the table with values and just close it and next we want to create one more table how to create one more table just go to create tab under create tab that you can see the table once again it'll be the step navigate to create tab where you can see the table icon just click click on this icon in order to create a new table when you click on this icon it will generate a table one a new table again we want to navigate to design view how to navigate so go to view and descend when you click on design view it will ask for the table name what is a table name subject click ok so now we want to design sid a physics chemistry math and followed by english so yes underscore id it will be of short text and followed by physics it will be of data type s and no and chemistry it will be of data type s and no and math so again xml followed by english again s and no so after designing the table just click the close button in order to save it again the dialogue will come do you want to save the changes to the design of the table subject yes and now you want to enter the values for the table subject just double click the table subject in order to enter the value let us say yes one not one let us say physics say yes one not two they say chemistry yes one or three at the same math and as104 let us say english and close the tape now there are two tables we want to create one more table so how to create a new table just go to create tab where you can see the table icon just click on the table icon again i'll repeat the steps navigate to create tab and where you can see the table icon just click on this icon in order to create a new tab so here you can see the new table was created under the name table one again we want to navigate to design view how to navigate to design view right click and navigate to design view and it will ask for the table name let us save fees click ok so what are the field names sid and paid so yes underscore id it will be of short text and primary key and followed by paid it will be of yes or no so once you finish the table design just click the close icon in order to save it and save it now double click this face table in order to enter the values it is as101 it's paid let's say yes one or two unpaid is 103 let us say paid s or not four okay unpaid or fake and close it now we have created the three tables next step we want to create the relationship how to create the relationship here you can see the database tool tab is there just click on this tab or navigate to this tab in database tools where you can see the relationship icon once again i repeat the steps navigate to database tools in database tools where you can see the relationship icon just click on this icon when you click on this icon show table pop-up window will be opened so where you can see all the tables right here what are the tables have we have created will be listed right here select the tables we wish to create relationship today we are going to use all the three tables i am going to select all the three tables and click on add and close it now you can see all the tables are right here now we are going to create the relationship so student subject and fees so how to create the relationship now so here you can see edit relationship icon is there just click on edit relationship icon when you click on the edit relationship icon it will takes you to a pop-up window edit relationship where you can see the option create new just click on create new so it will be like student and it will be like subject so s id and s id click ok so then click on the option enforce referential integrity and then click create in order to create it now wow we can create a one-to-one relationship between the tables student and subject now we want to create a one-to-one relationship between the student and fees how to do so so just again click on edit relationship icon and go to create new now this time left table name will be like student and write table name will be fees and left column name will be sid and right column name will be sid click ok and check the option enforce referential integrity click create now you have created a one-to-one relationship between student and fees now you wish to create one-to-one relationship between the subject and fees you are allowed to do so how again click on edit relationship icon create new and this time subject and right hand table will be fees left and column name will be sid and right column name will be sid click ok and enforce differential integrity click create now you can see so you have created a one-to-one relationship between the table student and subject and between student and fees and between the tables fees and subject now we want to save the relationship how so on the top of the relationship here right click save and if you wish to generate the relationship report you are allowed to do so how here you can see relationship report icon is there just click on relationship report icon in order to generate the relationship report now you have generated the relationship report just close this preview so now you have created the three tables and save it now you have created the three tables and you have saved the relationship report i think you guys have enjoyed this video thanks for watching this video

Channel: Karthik Dakshinamurthi

Views: 1,554

Rating: 4.8333335 out of 5

Keywords: #Microsoft Office, #Microsoft Access, #Access Table and Relationship, #Access Yes/No Datatype, #Access Short text and Long Text Datatype, #Access one to one Relationship, #How to create Tables and Relationship in MS Access, #MS Access Table, #Database Relationship, #One to One Relationship, #One to one Relationship using Three Tables, #Enforce Referential Integrity in Relationship, #one to one Relationship with Enforce Referential Integrity.

Id: CpNp1-rHOKk

Length: 12min 29sec (749 seconds)

Published: Sun Aug 02 2020