We will understand the need to create relationships between related tables. One of the goals of good database design is to remove data redundancy.
To achieve that goal, you divide your data into many subject-based tables so that each fact is represented only once.
To do this, all the common fields which are related to each other are placed in one table.
To do this step correctly, you must first understand the relationship between your tables, and then specify these relationships in your Access database.
Why Create Table Relationships? Microsoft Access uses table relationships to join tables when you need to use them in a database object. There are several reasons why you should create table relationships before you create other database objects, such as forms, queries, macros, and reports.
To work with records from more than one table, you often must create a query that joins the tables.
The query works by matching the values in the primary key field of the first table with a foreign key field in the second table.
When you design a form or report, MS Access uses the information it gathers from the table relationships you have already defined to present you with informed choices and to prepopulate property settings with appropriate default values.
When you design a database, you divide your information into tables, each of which has a primary key and then add foreign keys to related tables that reference those primary keys.
These foreign key-primary key pairings form the basis for table relationships and multi-table queries.
Let us now add another table into your database and name it tblHRData using Table Design as shown in the following screenshot.
Click on the Save icon as in the above screenshot.
Enter tblHRData as table name and click Ok.
tblHRData is now created with data in it.
Microsoft Access - One-To-One Relationship Let us now understand One-to-One Relationship in MS Access. This relationship is used to relate one record from one table to one and only one record in another table.
Let us now go to the Database Tools tab.
Click on the Relationships option.
Select tblEmployees and tblHRData and then click on the Add button to add them to our view and then close the Show Table dialog box.
To create a relationship between these two tables, use the mouse, and click and hold the EmployeeID field from tblEmployees and drag and drop that field on the field we want to relate by hovering the mouse right over EmployeeID from tblHRData. When you release your mouse button, Access will then open the following window:
The above window relates EmployeeID of tblEmployees to EmployeeID of tblHRData. Let us now click on the Create button and now these two tables are related.
The relationship is now saved automatically and there's no real need to click on the Save button. Now that we have the most basic of relationships created, let us now go to the table side to see what has happened with this relationship.
Let us open the tblEmployees table.
Here, on the left-hand side of each and every record, you will see a little plus sign by default. When you create a relationship, Access will automatically add a sub-datasheet to that table.
Let us click on the plus sign and you will see the information that is related to this record is on the tblHRData table.
Click on the Save icon and open tblHRData and you will see that the data we have entered is already here.
Microsoft Access - One-To-Many Relationship
The vast majority of your relationships will more than likely be this one to many relationships where one record from a table has the potential to be related to many records in another table.
The process to create one-to-many relationship is exactly the same as for creating a one-to-one relationship.
Let us first clear the layout by clicking on the Clear Layout option on the Design tab.
We will first add another table tblTasks as shown in the following screenshot.
Click on the Save icon and enter tblTasks as the table name and go to the Relationship view.
Click on the Show Table option.
Add tblProjects and tblTasks and close the Show Table dialog box.
We can run through the same process once again to relate these tables. Click and hold ProjectID from tblProjects and drag that all the way over to the ProjectID from tblTasks. Further, a relationships window pops up when you release the mouse.
Click the Create button. We now have a very simple relationship created.
Microsoft Access - Many-To-Many Relationship
In this chapter, let us understand Many-to-Many Relationship. To represent a many-tomany relationship, you must create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships. To do so, we also need to add a junction table. Let us first add another table tblAuthers.
Let us now create a many-to-many relationship. We have more than one author working on more than one project and vice versa. As you know, we have an Author field in tblProjects so, we have created a table for it. We do not need this field any more.
Select the Author field and press the delete button and you will see the following message.
Click Yes. We will now have to create a junction table. This junction table have two foreign keys in it as shown in the following screenshot.
These foreign key fields will be the primary keys from the two tables that were linked together — tblAuthers and tblProjects.
To create a composite key in Access, select both these fields and from the table tools design tab, you can click directly on that primary key and that will mark not one but both of these fields.
The combination of these two fields is the tables’ unique identifier. Let us now save this table as tblAuthorJunction.
The last step in bringing the many-to-many relationships together is to go back to that relationships view and create those relationships by clicking on Show Table.
Select the above three highlighted tables and click on the Add button and then close this dialog box.
Click and drag the AuthorID field from tblAuthors and place it on top of the tblAuthorJunction table AuthorID.
The relationship you’re creating is the one that Access will consider as a one-to-many relationship. We will also enforce referential integrity. Let us now turn on Cascade Update and click on the Create button as in the above screenshot.
Let us now hold the ProjectID, drag and drop it right on top of ProjectID from tblAuthorJunction.
We will Enforce Referential Integrity and Cascade Update Related Fields.
The following are the many-to-many relationships.
To do this, all the common fields which are related to each other are placed in one table.
To do this step correctly, you must first understand the relationship between your tables, and then specify these relationships in your Access database.
Why Create Table Relationships? Microsoft Access uses table relationships to join tables when you need to use them in a database object. There are several reasons why you should create table relationships before you create other database objects, such as forms, queries, macros, and reports.
To work with records from more than one table, you often must create a query that joins the tables.
The query works by matching the values in the primary key field of the first table with a foreign key field in the second table.
When you design a form or report, MS Access uses the information it gathers from the table relationships you have already defined to present you with informed choices and to prepopulate property settings with appropriate default values.
When you design a database, you divide your information into tables, each of which has a primary key and then add foreign keys to related tables that reference those primary keys.
These foreign key-primary key pairings form the basis for table relationships and multi-table queries.
Let us now add another table into your database and name it tblHRData using Table Design as shown in the following screenshot.
Microsoft Access - One-To-One Relationship Let us now understand One-to-One Relationship in MS Access. This relationship is used to relate one record from one table to one and only one record in another table.
Let us now go to the Database Tools tab.
Let us open the tblEmployees table.
The vast majority of your relationships will more than likely be this one to many relationships where one record from a table has the potential to be related to many records in another table.
The process to create one-to-many relationship is exactly the same as for creating a one-to-one relationship.
In this chapter, let us understand Many-to-Many Relationship. To represent a many-tomany relationship, you must create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships. To do so, we also need to add a junction table. Let us first add another table tblAuthers.
To create a composite key in Access, select both these fields and from the table tools design tab, you can click directly on that primary key and that will mark not one but both of these fields.
The last step in bringing the many-to-many relationships together is to go back to that relationships view and create those relationships by clicking on Show Table.
Select the above three highlighted tables and click on the Add button and then close this dialog box.
0 Comments