Queries and Relationships

Introduction

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, or a Recordset, 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 gender. A question that comes is mind: 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.

When building a query, you select columns and ask Microsoft Access to isolate them as being part of the query. Most of the time, you will want only columns that include a type of validation of your choice. This validation is called a criterion. An inner join is the kind of query that presents only columns 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 dialog box, 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 the INNER JOIN expression in the statement.

Practical Learning: Creating Inner Join Relationships

1. Open the Music Collection1 database that you started in the previous lesson
2. On the Database toolbar, click the Relationships button
3. On the Relationships window, click the joining line between the Artists table and the MusicAlbums table to select it
4. On the main menu, click Relationships -> Edit Relationship…
5. 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)
    
6. Click OK twice
7. Open the Rockville Techno database
8. Open the CompanyAssets table in Design View
9. Right-click Notes and click Insert Rows
10. 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.

11. Set its Data Type to Number
12. In the lower section of the view, make sure the Field Size is set to Long Integer. In the Caption field, type Assigned To
13. Save the table and switch it to Datasheet View
14. 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

15. Close the CompanyAssets table
16. In the Database window, click the Forms button
17. To create a new query, on the main menu, click Insert -> Query and, in the New Query dialog box, double-click Design View
18. In the Show Table dialog box, double-click Employees and CompanyAssets then click Close
19. From the Employees list, drag EmployeeID and drop it on top of AssignedTo in the CompanyAssets list:
     
20. 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
21. Notice that the first radio button that represents an inner join is selected
    Click OK
22. To select the fields, in the CompanyAssets list, double-click AssetType, Make, Model, and PurchasePrice
23. From the Employees list, double-click LastName and First Name
     
24. To view the SQL statement, right-click the title bar of the window and click SQL View:
     
    
25. To view the result of the query, on the Query Design toolbar, click Run 
     
26. 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
27. Open the CompanyAssets table in Design View
28. Change the Field Name of AssignedTo to EmployeeID and make sure it keeps its Caption as Assigned To
29. Save the table and close it
30. 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
31. 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…
32. Right-click an empty area in the Relationships window and click Show Table
33. 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
34. Drag EmployeeID from the Employees list and drop it on top of EmployeeID in the CompanyAssets list
35. 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
36. In the Relationships dialog box, click the Enforce Referential Integrity check box and click the check boxes under it
37. Click Create
38. Notice the 1 and infinity symbol between the CompanyAssets and the Employees tables
    Save and close the Relationships window
39. To add a table, right-click an empty area on the Select Query window and click Show Table
40. In the list of tables, double-click Employees and click Close
41. To select the fields, in the CompanyAssets list, double-click AssetType, Make Model, and PurchasePrice
42. From the Employees list, double-click LastName and First Name
     
43. To view the SQL statement, right-click the title bar of the window and click SQL View
44. To view the result of the query, on the Query Design toolbar, click Run . The result is the same as the above result
45. After viewing the query, close it. When ask whether you want to save it, click Yes
46. 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 columns that had entries based on the established relationships. Columns that didn't follow the rules were excluded. Instead of excluding columns, 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 (or query). The other table is referred to as the Right table (or query). Based on this, there are two types of outer joins: the left join is represented in SQL as LEFT JOIN and the right join is 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.

1. Open the Video Collection1 database and, on the Database toolbar, click the Relationships button
2. In the Relationships button, double-click the joining line between Directors and Videos
3. In the Edit Relationship dialog box, click Join Type
4. In the Join Properties dialog box, read all options then click the second radio button
    
5. Click OK
6. In the same way, double-click each line and configure it with the second radio button of the Join Properties dialog box
7. Open the Music Collection1 database and open the Relationships window
8. Configure each relationship as follows:
    
9. If you are using Microsoft Access 2000 and above, to create a report for the relationships, on the main menu, click File -> Print Relationships…
10. To save the report, press Ctrl + S 
11. Type Music Collection Diagram as the name of the report and press Enter 
12. Close the Relationships window
13. Open the Video Collection database and its Relationships window. Then, configure all relationships as done for the Music Collection1 database:
     
14. Save and close the Relationships window
15. Open the Rockville Techno database and, on the Database toolbar, click the Relationships button
16. Right-click the line between Employees and CompanyAssets then click Edit Relationship
17. In the Relationships dialog box, click Join Type and click the second radio button
18. Click OK twice
19. Save and close the Relationships window
20. On the Database window, click the Tables button and click CompanyAssets
21. On the main menu, click Insert -> Query. Since Design View is selected, click OK
22. As done previously, add the Employees table to the query
23. From the CompanyAssets list, double-click AssetType, Make Model, and PurchasePrice
24. From the Employees list, double-click First Name and LastName
25. 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
     
26. To run the query, on the main menu, click View -> Datasheet View
     
27. After viewing the query, close it. When asked whether you want to save it, click Yes
28. Type Assets Already Assigned and press Enter
29. 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
30. As done previously, besides the CompanyAssets table, add the Employees table to the query
31. From the CompanyAssets list, double-click AssetType, Make, Model, and PurchasePrice
32. From the Employees list, double-click First Name and LastName
33. Right-click the line between CompanyAssets and Employees then click Join Properties
34. In the Join Properties dialog box, read the text of all radio buttons. Click the third radio button
     
35. Click OK
36. 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
     
37. To run the query, on the main menu, click View -> Datasheet View
     
38. After viewing the query, close it. When asked whether you want to save the query, click Yes
39. Type Assets Not Yet Assigned and click OK
40. Close Microsoft Access

MOUS Topics

Exercises 

• Open the Watts A Loan database and configure the relationships among its tables as follows:
   
  
  
  Save and close the Relationships window

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