When creating relationships among tables, we were selecting the primary key of one table and the foreign key of a dependent table to join them. Once such a relationship is created, you can create a query that combines both tables to create a set of records, also called a record set, that would include either all records or isolate only the records that have entries. For example, imagine you have created a Persons table as follows where the Gender of a record is selected from a lookup field:

Suppose you want to create a query that includes the persons of this table and their genders. A question that comes is mind is: Do you want to create a list of only people who can be recognized by their gender, or do you want the list to include everybody? This concept leads to what are referred to as inner joins and outer joins of queries. There are two ways you can get such queries: you can prepare the relationship between two tables to be aware of this type of relationship or you can directly create it when designing a query.

Inner Joins

When building a query, you select fields and ask Microsoft Access to isolate them as being part of the query. Most of the time, you will want only fields that include a type of validation of your choice (we will call it a criterion). An inner join is the kind of query that presents only fields that have matching entries on both tables of a relationship. For example, from the above table, if you want to create a query that includes only persons whose records contain the gender, you would create it as an inner join. In such a query, only the 1st, the 2nd, the 4th, and the 7th records would display:

To specify that a relationship is inner join, after creating the relationship, while in the Relationships, you can display its Edit Relationship dialog box and click Join Type… The Join Properties dialog box presents three options that allow you to define the direction of the relationship between the tables.

To specify an inner join in a SQL statement, you include INNER JOIN in the statement

Practical
Learning: Creating Inner Join Relationships

Open the Music Collection1 database that you started in the previous lesson

On the Database toolbar, click the Relationships button

On the Relationships window, click the joining line between the Artists table and the MusicAlbums table to select it

On the main menu, click Relationships -> Edit Relationship…

After making sure that ArtistID and RecordingArtistID are selected in the combo boxes, click the Join Type button
In the Join Properties dialog box, click the first radio button (it should be selected already)

Click OK twice

Open the Rockville Techno database

Open the CompanyAssets table in Design View

Right-click Notes and click Insert Rows

In the new empty field, type AssignedTo

We are setting the name of this field to AssignedTo. This field will be linked to the EmployeeID field of the Employees table. In reality, you should set the name of such a field similar to the primary key it is linked to. We are using a different name here just to demonstrate how to create a relationship in the query Design View. In the future, always give the same name as the field it is related to, in the parent table. In this case, it should have been named EmployeeID.

Set its Data Type to Number

In the lower section of the view, make sure the Field Size is set to
Long Integer. In the Caption field, type Assigned To

Save the table and switch it to Datasheet View

In the Assigned To column, type the numbers as follows:

Asset ID

Asset Type

Assigned To

1

Printer

2

Computer

2

3

Laptop

1

4

Printer

5

Digital Camera

6

Computer

7

Computer

1

8

Computer

4

Close the CompanyAssets table

In the Database window, click the Forms button

To create a new query, on the main menu, click Insert -> Query and, in the New Query dialog box, double-click Design View

In the Show Table dialog box, double-click Employees and CompanyAssets then click Close

From the Employees list, drag EmployeeID and drop it on top of AssignedTo in the CompanyAssets list:

Notice that a line has been drawn between the tables and it joins both fields.
Right-click the line that joins the tables and click Join Properties

Notice that the first radio button that represents an inner join is selected
Click OK

To select the fields, in the CompanyAssets list, double-click AssetType, Make Model, and PurchasePrice

From the Employees list, double-click LastName and First Name

To view the SQL statement, right-click the title bar of the window and click SQL View:

To view the result of the query, on the Query Design toolbar, click Run

Notice that, with this inner join, only the asset(s) assigned to an employee are displayed. Any asset not assigned to an employee does not display.
After viewing the query, close it. When asked whether you want to save it, click No

Open the CompanyAssets table in Design View

Change the Field Name of AssignedTo to EmployeeID and make sure it keeps its Caption as Assigned To

Save the table and close it

As the CompanyAssets table is still selected in the Tables section of the Database window, to create a new query, on the Database toolbar, click the arrow of the New Object button and click Query. In the New Query dialog box, while Design View is selected, click OK

Notice that the CompanyAssets table is already selected.
To create a relationship between the CompanyAssets table and the Employees table, right-click an empty area in the upper section of the window and click Relationships…

Right-click an empty area in the Relationships window and click Show Table

In the list of tables, double-click CompanyAssets (the Employees table should already be in the window; if not, add it also) and close the Show Table dialog box

Drag EmployeeID from the Employees list and drop it on top of EmployeeID in the CompanyAssets list

After making sure that EmployeeID is selected in both combo boxes, click Join Type. In the Join Properties dialog box, make sure the first radio button is selected and click OK

In the Relationships dialog box, click the Enforce Referential Integrity check box and click
the check boxes under it

Click Create

Notice the 1 and infinity symbol between the CompanyAssets and the Employees tables
Save and close the Relationships window

To add a table, right-click an empty area on the Select Query window and click Show Table

In the list of tables, double-click Employees and click Close

To select the fields, in the CompanyAssets list, double-click AssetType, Make Model, and PurchasePrice

From the Employees list, double-click LastName and First Name

To view the SQL statement, right-click the title bar of the window and click SQL View

To view the result of the query, on the Query Design toolbar, click Run
. The result is the same as the above result

After viewing the query, close it. When ask whether you want to save it, click Yes

Type Assets Assigned To Employees and press Enter

Outer Joins

The queries we have used so far and that were based on related tables allowed us to get only the fields that had entries based on the established relationships. Fields that did not follow the rules were excluded. Instead of excluding fields,
the SQL allows you to create a query that includes all fields, not just those that follow rules, as long as the records are part of either
table. Such a query is referred to as outer join. To manage the result of this type of query,
the SQL considers the direction of a relationship.

When creating relationships, we learned to drag a primary key from one table, the parent, to a dependent table, the child. In this type of relationship, the table (or query) that holds the origin of the relationship is referred to as the Left table. The other table is referred to as the right table. Based on this, there are two types of outer joins: the left join represented in SQL as
LEFT JOIN and the right join represented by RIGHT JOIN.

As done with the inner join relationship, the left and right joins can be prepared in the Relationships window on tables. If the query has already been created and you want to change its direction, you can change it in the Design View of the query.

Practical
Learning: Creating Outer Join Relationships

Open the Video Collection1 database and, on the Database toolbar, click the Relationships button

In the Relationships button, double-click the joining line between Directors and Videos

In the Edit Relationship dialog box, click Join Type

In the Join Properties dialog box, read all options then click the second radio button

Click OK

In the same way, double-click each line and configure it with the second radio button of the Join Properties dialog box

Open the Music Collection1 database and open the Relationships window

Configure each relationship as follows:

If you are using Microsoft Access 2000 and above, to create a report for the relationships, on the main menu, click File -> Print Relationships…

To save the report, press Ctrl + S

Type Music Collection Diagram as the name of the report and press Enter

Close the Relationships window

Open the Video Collection database and its Relationships window. Then, configure all relationships as done for the Music Collection1 database:

Save and close the Relationships window

Open the Rockville Techno database and, on the Database toolbar, click the Relationships button

Right-click the line between Employees and CompanyAssets then click Edit Relationship

In the Relationships dialog box, click Join Type and click the second radio button

Click OK twice

Save and close the Relationships window

On the Database window, click the Tables button and click CompanyAssets

On the main menu, click Insert -> Query. Since Design View is selected, click OK

As done previously, add the Employees table to the query

From the CompanyAssets list, double-click AssetType, Make Model, and PurchasePrice

From the Employees list, double-click First Name and LastName

To view the SQL statement, right-click the title bar of the window and click SQL View. Notice the
LEFT JOIN clause in the statement

To run the query, on the main menu, click View -> Datasheet View

After viewing the query, close it. When asked whether you want to save it, click Yes

Type Assets Already Assigned and press Enter

To follow the same steps to create a query with the same fields,
once again, on the main menu, click Insert -> Query. Since Design View is selected, click OK

As done previously, besides the CompanyAssets table, add the Employees table to the query

From the CompanyAssets list, double-click AssetType, Make,
Model, and PurchasePrice

From the Employees list, double-click First Name and LastName

Right-click the line between CompanyAssets and Employees then click Join Properties

In the Join Properties dialog box, read the text of all radio buttons. Click the third radio button

Click OK

To view the SQL statement, right-click the title bar of the window and click SQL View. Notice the
RIGHT JOIN clause in the statement

To run the query, on the main menu, click View -> Datasheet View

After viewing the query, close it. When asked whether you want to save the query, click Yes

Type Assets Not Yet Assigned and click OK

Close Microsoft Access

Lesson Summary

MOUS Topics

S32

Create and modify a multitable select query

S33

Establish relationships

Exercises

Watts A Loan

Open the Watts A Loan database and configure the relationships among
its tables as follows:

Save and close the Relationships window

Yugo National Bank

Open the Yugo National Bank and open its Relationships window.
Configure the directions of the relations as follows then save the
diagram: