MS Access - Joins
A database is a set of tables of data that allow logical relationships with each other. You use relationships to connect tables by fields that they have in common. A table can be part of any number of relations, but each relation always has exactly two tables. In a query, a relation is represented by a Join.
What is a join
A join specifies how to combine records from two or more tables in a database. Conceptually, a join is very similar to a table relationship. In fact, joins are meant to query relationships with tables.
Here are the two basic types of joins that we will discuss in this chapter -
- The inner join
- The outer join
Both can easily be created from 'a query design view .
Now let's understand Inner join -
- The most common type of join is an inner join which is also the default join type in Microsoft Access.
- Inner Join will only show rows or records where the joined fields of the two tables are equal.
- This type of join examines these common fields and the data they contain. It only displays matches.
Now let's see what an outer join -
- An outer join displays all rows from one table and only rows or records from the other table where joined fields are equal.
- In other words, an outer join displays all rows from one table and only matching rows from the other table.
There are other types of joins as well -
Left outer join and right outer join
Now let us understand Left Outer Join and Right Outer Join -
- You can choose the table which will display all rows.
- You can create a left outer join that will include all rows from the first table.
- You can create a right outer join that will include all rows from the second table.
Now let's go to the Create tab and create a query from the Design View . Select tblProjects and tblTasks and close the Show Table dialog as in the following screenshot.
Add field ProjectName from tblProjects and TaskTitle, StartDate and DueDate from the tblTasks table.
Now let's run the query.
We only display records from a few projects. Some of these projects have a lot of tasks associated with this project and these information is linked through the ProjectID field.
When we create this query in Microsoft Access, Access takes this relationship from relationships that we havens created.
By default, it creates what is called an Inner join between these two fields , between these two tables, and it 's this is how it relates this information from these two tables.
It only shows us the matches, so when we run this query there are a lot of other projects listed in tblProjects that don 't 'appear as part of our records defined in this query, and that's because of the way these two tables are joined, through this inner join, which is again, this default join for any query.
However, if you want to change the relationship, let's say you want to create an outer join, or in other words, show all projects in tblProjects , every single record that is in this table, as well as all tblTasks tasks - Open join properties; nowe can do this just by double clicking on the relation line.
Access will display the name of the left table and the name of the right table in the Join Properties dialog.
The name of the left column and the name of the right column and the first radio button should only include rows where the join fields of the two tables are equal and c 'is the inner join and this is what is selected by default when creating relationships, when creating a join in the query, but you can change it.
We also have two other options; we can include all records from tblProjects , and only those from tblTasks where the joined fields are equal and this one is Left Outer Join.
We have a third option, include all records from tblTasks and only records from tblProjects where joined fields are equal and this one is Right Outer Joinot.
Here are the different types of joins that you can easily create from Design mode. Let's choose the second option, which is Left Outer Join, and click on Ok.
Now let's look at the other steps -
When you look at the relation line, you will see a small arrow pointing to ProjectID in tblTasks . When you run this query, you will see the following results.
As you can see, it shows us every project name whether or not it has an associated task. You will see Also a bunch of null fields. All of this will be empty because there is no associated information in tblTasks , where these fields come from. Now let's go back to the Design View b> and double-click on the relation line.
In the Join Properties dialog box, select the third option that corresponds to the right outer join and click Ok .
Now look at our relationship line. You will see that a small arrow now points to ProjectID in tblProjects . When you run this query, you will see thefollowing results.
Another type of join is self-join. A self-join relates the corresponding fields from the same table. For example, look at the employee table with a supervisor field, which refers to the same type of number stored in another field in the same table - employee ID.
If we wanted to know who Kaitlin Rasmussen's supervisor was it ' that is, we will need to take the number stored in that supervisor field and look it up in the exact same table in this employee ID field in order to know that Charity Hendricks is the supervisor.
This table is not the ideal structure for a final database relatio because it is not normalized.
If we have a situation where we want to create a query that simply lists the employee names next to the names of their supervisors, there is no easy way to query unless we create a self-join.
To see a self-join create a table with the fieldsvants and enter data.
Here we wantons recreate a list with the first name of the employee then the last name of the supervisor. Let's create a query from the query design view .
Now add tblEmployees tables.
Close this dialog.
Now add the first and last names of our employees.
We now need a way to create a relation between this table and itself To do this, we need to open the Show Table dialog and add tblEmployees once again.
We have created another copy of the same table in this query view. Now we need to create a self-join. To do this, click Supervisor in the tblEmployees table and hold the mouse button down and drop it just over the- above the EmployeeID in this copied table - tblEmployees_1 . Next, add the first and last name of the copied table.
Now let's run your query and you will see the following results.
It displays the names of the employees next to the names of their superiors. And here is how to create a self-join in Microsoft Access .