MS Access - Joins

A database is a collection of tables of data that allow logical relationships to each other. You use relationships to connect tables by fields that they have in common. A table can be part of any number of relationships, but each relationship always has exactly two tables. In a query, a relationship is represented by a Join.

What is 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 to queries what relationships are to tables.

The following are the two basic types of joins that we will be discussing in this chapter −

• The inner join
• The outer join

Both of which can easily be created from a queries design view.

Inner Join

Let us now 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 display only the rows or records where the joined fields from both tables are equal.
• This join type looks at those common fields and the data contained within. It only displays the matches.

Outer Join

Let us now understand what an Outer Join is −

• An outer join displays all rows from one table and only those rows or records from the other table where the joined fields are equal.
• In other words, an outer join shows all rows from one table and only the corresponding rows from the other table.

There are other Join types too −

Left Outer Join & Right Outer Join

Let us now understand Left Outer Join and Right Outer Join −

• You can choose the table that will display all rows.
• You can create a Left Outer Join which will include all the rows from the first table.

• You can create a Right Outer Join that will include all the rows from the second table.

Let us now go to the Create tab and create a query from the Design View. Select tblProjects and tblTasks and close the Show Table dialog box as in the following screenshot.

Add the ProjectName field from tblProjects, and TaskTitle, StartDate and DueDate from tblTasks table.

Let us now run the query.

We are only displaying records from a few projects. A couple of these projects have a lot of tasks associated with that project and this information is related through ProjectID field.

• When we create this query in Microsoft Access, Access is taking this relationship from the relationships we have created.

• By default, it is creating what's known as an Inner Join between these two fields, between these two tables, and that is how it is relating this information together from these two tables.

• It is showing us only the matches, so when we run this query, there are a lot of other projects listed in tblProjects that do not appear as part of our records set in this query, and that is because of how these two tables are joined together, through that Inner Join, which is again, that default Join for any query.

However, if you want to alter the relationship, Let us say you want to create an outer join, or in other words, show all of the projects from tblProjects, every single record that is in that table, along with all of the tasks from tblTasks — Open join properties; we can do this simply by double-clicking on the relationship line.

Access will display the left table name and the right table name in Join Properties dialog.

• The left column name and the right column name and the first radio button is to only include rows where the join fields from both tables are equal and that is the inner join and that is what is selected by default when creating relationships, when creating a join in the query, but, you can change it.

• We have two other options as well; we can include all records from tblProjects, and only those records 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 those records from tblProjects where the joined fields are equal and this one is Right Outer Join.

Those are the different types of joins you can create easily from the Design View. Let us select the second option, which is Left Outer Join, and click Ok.

Let us now look into the other steps −

When you look at the relationship line, you will see a little arrow pointing towards ProjectID in tblTasks. When you run this query, you will see the following results.

As you can see that it is showing us every single project name, whether or not it has a related task. You will also see a bunch of null fields. All of this will be blank because there is no related information in tblTasks, where these fields come from. Let us now go to the Design View again and double-click on the relationship line.

In the Join Properties dialog box, select the third option which is for the Right Outer Join and click Ok.

Now look at our relationship line. You will see that a little arrow is now pointing towards ProjectID in tblProjects. When you run this query you will see the following results.

Self-Joins

Self-join is another type of Join. A Self-join relates matching fields from within the same table. For example, look at the employee's table with a supervisor field, which references the same kind of number stored in another field within the same table — the employee ID.

If we wanted to know who Kaitlin Rasmussen's supervisor is, we will have to take the number stored in that supervisor field and look it up within the exact same table in that employee ID field in order to know that Charity Hendricks is the supervisor.

This table is not the ideal structure for a relational database because it's not normalized.

If we have a situation where we want to create a query that just lists the employee names alongside the names of their supervisors, there is no easy way we can query that unless we create a Self-join.

To see a Self-join, create a table with the following fields and enter some data.

Here we want to create a list again with the first name of the employee and then the name of the supervisor. Let us create a query from the query design view.

Now, add tblEmployees tables.

Close this dialog box.

Now, add the first name and last name for our employees.

We now need a way to create a relationship between this table and itself. To do that, we need to open the show table dialog box and add tblEmployees one more time.

We have created another copy of the same table in this query view. Now, we need to create Self-join. To do that, click on Supervisor in tblEmployees table and hold the mouse button and drop it right on top of the EmployeeID in that copied table — tblEmployees_1. Then, add the first name and last name from that copied table.

Let us now run your query and you will see the following results.

It displays the names of the employees along side the names of their supervisors. And, this is how you create a Self-join in Microsoft Access.