creatingrelationshipsinaccess This is a topic that many people are looking for. passionistsisters.org 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, passionistsisters.org would like to introduce to you How to create table relationships in Access lynda.com tutorial. Following along are instructions in the video below:
“2010 is considered a relational database. Meaning that the data is split into tables that that are interconnected in this lesson. We ll create relationships between our four tables and the referential integrity start by clicking on the database tools tab then on the relationships button click on the show tables button and then double click each of the four tables now i have a tendency to click on add thinking i m going to close the window so if you do this it will accidentally add in a repeat of that same table you can see the underscore. One go ahead and close the show table button click on that extra table and hit the delete button on your keyboard and it will disappear.
Now hold your cursor over the bottom edge of the customers table. Until you get a double headed arrow. And then click and drag down. So that you can see the rest of your fields.
And do the same thing on the sales reps table. So let s start with our customers table. One of the fields is sales reps. And it gets that info from the sales reps table.
So. Let s drag. The primary key field m. Pi.
.
D. From the sales. Reps table and drop. It on sales reps.
Here. In the customers table. It brings up an edit relationships window. Showing the the source field and the destination field and we want to click in the check box to enforce referential integrity.
Then check. Both cascade update. And cascade. Delete.
Related records. Cascade. Update. Means.
.
If we make any changes to the employee. Id field. It will make the same changes in any other tables that use that field in our case. If we change the sales reps id.
It would make the same changes in the customer table. If we didn t check this we could potentially find sales reps in the customer table that no longer existed those are called orphaned records cascade delete means if we delete a sales rep from the table. It would delete that id in the customers table as well leaving the field blank. We could then assign a new sales rep to that customer.
We also have a join type to set click this button here there are three states. Only include rows. Where the join fields from both tables are equal include all records from sales reps and only those records from customers. The joined fields are equal include all records from customers and only those records from sales reps.
Where the joint fields are equal. Now most of the time we use the first one but in cases where we would want to see reports that included the sales reps even if they didn t have any assigned customers. We would choose the second option if we wanted to run reports that included all of our customers. Even if they didn t have any sales reps.
.
We d choose that option. Let s go ahead and choose option number two and then click ok. Now notice down. Here.
The one to many designation. Indicating that there can only be one of each rep. And that each rep can have many customers then click create you now see. A black line running between the sales.
Reps table and the customers table. There s a one right here. Indicating the one part of the relationship. And there s an infinity symbol right here indicating the many customers that each rep might have notice that the line goes under the products table you can drag the tables and move them around to your liking in fact.
Let s move sales reps over to the left and move all of these tables over next. The customers. All place orders. So let s drag.
.
The cust id field over to the customer field in the orders table enforce referential integrity and cascade update and delete so that if we delete a customer from the database. All of their orders will disappear. Too we don t need to adjust the joint type. We won t ever have customers that haven t placed an order and we won t have orders without customers click create and you ll see the one to many relationship.
There s no arrow here the way. There is here because we did a normal join type instead of a type two now let s do the same from the sku in the product table pick it up and drag it to the product in the orders table enforce referential integrity. But only the cascade update. We don t want to cascade delete.
Because if we discontinue a product. We don t want that products sku to disappear out of an order. We also don t need to change the join type click create to finish now click the close button and access will ask to save the changes say yes now all of our tables are linked together and access understands. Which pieces of data interrelate relationships will also allow us to bring data together from the different tables into ” .
.
Thank you for watching all the articles on the topic How to create table relationships in Access lynda.com tutorial. All shares of passionistsisters.org 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.
description:
tags:
