Lessons Logo

Queries and Relationships

 

Introduction

Queries and Relationships

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:

 

The Persons Table

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:

Select Query

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

  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 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

  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
 

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:
     
  

Previous Copyright © 2002-2004 Yevol Next