Home

Queries and Data Joins

Introduction

When creating a query that involves more than one table, you don't have to first link the tables in the Relationships window. If the tables have a primary and a foreign keys, you can create their joins directly in the Design View of a query, in the Object Filter window, or in a Query Builder. In fact, the query has its own complete functionality when it comes to data joins.

Practical Learning: Introducing Data Joins in Queries

  1. On the Ribbon, click File and click New
  2. Click Blank Desktop Database
  3. Set the file name as Lambda Square Apartments1 and click Create
  4. On the Ribbon, click File and click Options
  5. On the left side, click Current Database
  6. On the right side, under Document Window Options, click the Overlapping Windows radio button, and click OK
  7. Read the message box and click OK
  8. On the Ribbon, click File and click Close
  9. In the list of files, click Lambda Square Apartments
  10. On the Ribbon, click External Data
  11. In the Import & Link section, click the Excel button Excel
  12. Click the Browse button
  13. From the resources that accompany these lessons, select the Lambda Square Apartments spreadsheet and click Open
  14. On the Get External Data dialog box, click OK
  15. In the first page of the wizard, click the Show Named Ranges radio button and click MaritalsStatus and click Next
  16. On the second page of the wizard, click First Row Contains Column Headings and click Next
  17. In the third page of the wizard, click Next
  18. Click Firat Row Contains Column Headings and click Next
  19. Click the arrow of the combo box and select MaritalStatus
  20. Click Next
  21. Accept the name of the table as MaritalsStatus and click Finish
  22. On the dialog box, click Close
  23. In the Navigation Pane, right-click the MaritalsStatus table and click Design View
  24. Change the following characteristics of the fields:
     
    Field Name Data Type Field Size
    MaritalStatus   20
    Description Long Text  
  25. Close the table
  26. When asked whether you want to save, click Yes
  27. You will also receive a message box that some data may be lost. Read it and click Yes
  28. On the Ribbon, click External Data
  29. In the Import & Link section, click the Excel button Excel
  30. Click the Browse button
  31. From the resources that accompany these lessons, select the Lambda Square Apartments spreadsheet and click Open
  32. On the Get External Data dialog box, click OK
  33. In the first page of the wizard, click the Show Named Ranges radio button, click OccupanciesStatus and click Next
  34. On the second page of the wizard, click First Row Contains Column Headings and click Next
  35. In the third page of the wizard, click Next
  36. Click the arrow of the combo box and select OccupancyStatus
  37. Click Next
  38. Accept the name of the table as OccupanciesStatus and click Finish
  39. On the dialog box, click Close
  40. In the Navigation Pane, right-click the OccupanciesStatus table and click Design View
  41. Change the following characteristics of the fields:
     
    Field Name Data Type Field Size
    OccupancyStatus   20
    Description Long Text  
  42. Close the table
  43. When asked whether you want to save, click Yes
  44. You will also receive a message box that some data may be lost. Read it and click Yes
  45. On the Ribbon, click External Data
  46. In the Import & Link section, click the Excel button Excel
  47. Click the Browse button
  48. From the resources that accompany these lessons, select the Lambda Square Apartments spreadsheet and click Open
  49. On the Get External Data dialog box, click OK
  50. In the first page of the wizard, click the Show Named Ranges radio button, make sure Apartments is selected and click Next
  51. On the second page of the wizard, make sure First Row Contains Column Headings and click Next
  52. In the third page of the wizard, as ApartmentCode is selected, set he Data Type to Short Text
  53. Click UnitNumber and set its Data Type to Integer
  54. Scroll to the right, click Bedrooms, and set the Data Type to Byte
  55. Click Bathrooms and set the Data Type to Single
  56. Click Next
  57. Click the arrow of the combo box, select ApartmentCode, and click Finish
  58. On the dialog box, click Close
  59. In the Navigation Pane, right-click Apartments and click Design View
  60. Change the following characteristics of the fields:
     
    Field Name Field Size
    ApartmentCode 10
    Status 20
  61. Close the table
  62. When asked whether you want to save, click Yes
  63. You will also receive a message box that some data may be lost. Read it and click Yes
  64. On the Ribbon, click External Data
  65. In the Import & Link section, click the Excel button Excel
  66. Click the Browse button
  67. From the resources that accompany these lessons, select the Lambda Square Apartments spreadsheet and click Open
  68. On the Get External Data dialog box, click OK
  69. In the first page of the wizard, click the Show Named Ranges radio button, click Employees and click Next
  70. On the second page of the wizard, click First Row Contains Column Headings and click Next
  71. In the third page of the wizard, as EmployeeNumber is selected, set the Data Type to Short Text, and click Next
  72. Click the arrow of the combo box and select EmployeeNumber
  73. Click Finish
  74. On the dialog box, click Close
  75. In the Navigation Pane, right-click the Employees table and click Design View
  76. In the top side of the window, right-click Title and click Insert Rows
  77. Change the following characteristics of the fields:
     
    Field Name Data Type Field Size Expression Caption
    EmployeeNumber   10    
    FirstName   20   First Name
    LastName   20   Last Name
    EmployeeName Calculated 20 [FirstName] & " " & [LastName]  
    Title   50    
  78. Close the table
  79. When asked whether you want to save, click Yes
  80. You will also receive a message box that some data may be lost. Read it and click Yes
  81. On the Ribbon, click External Data
  82. In the Import & Link section, click the Excel button Excel
  83. Click the Browse button
  84. From the resources that accompany these lessons, select the Lambda Square Apartments spreadsheet and click Open
  85. On the Get External Data dialog box, click OK
  86. In the first page of the wizard, click the Show Named Ranges radio button, click Registrations and click Next
  87. On the second page of the wizard, make sure First Row Contains Column Headings is selected, and click Next
  88. In the third page of the wizard, as RegistrationID is selected, set the Data Type to Long Integer
  89. Scroll to the right and click ProcessedBy
  90. Set the Data Type to Short Text
  91. Click NumberOfChildren and set the Data Type to Byte
  92. Click PropNbr and set the Data Type to Short Text
  93. Click Next
  94. Click the arrow of the combo box and select RegistrationID
  95. Click Finish
  96. On the dialog box, click Close
  97. In the Navigation Pane, right-click the Registrations table and click Design View
  98. Change the following characteristics of the fields:
     
    Field Name Field Size Caption
    ProcessedBy 10  
    FirstName 20 First Name
    LastName 20 Last Name
    MaritalSituation 20  
    PhoneNumber 50  
    EmailAddress 20  
    PropNbr 10  
  99. Close the table
  100. When asked whether you want to save, click Yes
  101. You will also receive a message box that some data may be lost. Read it and click Yes
  102. On the Ribbon, click External Data
  103. In the Import & Link section, click the Excel button Excel
  104. Click the Browse button
  105. From the resources that accompany these lessons, select the Lambda Square Apartments spreadsheet and click Open
  106. On the Get External Data dialog box, click OK
  107. In the first page of the wizard, click the Show Named Ranges radio button, click Payments and click Next
  108. On the second page of the wizard, make sure First Row Contains Column Headings is selected, and click Next
  109. In the third page of the wizard, as ReceiptNumber is selected, set the Data Type to Long Integer
  110. Scroll to the right, click ProcessedBy, and set the Data Type to Short Text
  111. Click RegistrationNumber and set the Data Type to Long Integer
  112. Click Next
  113. Click the arrow of the combo box and select ReceiptNumber
  114. Click Finish
  115. On the dialog box, click Close
  116. In the Navigation Pane, right-click the Payments table and click Design View
  117. Change the following characteristics of the fields:
     
    Field Name Field Size Field Size
    ProcessedBy   10
    Notes Long Text  
  118. Close the table
  119. When asked whether you want to save, click Yes
  120. You will also receive a message box that some data may be lost. Read it and click Yes

Cross Joins

A cross join creates a query of all records from two tables (or two existing queries, or a table and an existing query) as follows: the first record from the parent list is associated to each record from the child list, then the second record from the parent list is associated to each record from the child list, and so on. In this case also, there is no need for a common column between both tables. In other words, you will not use the ON clause.

The Relationships window implicitly supports cross join but they can be viewed and examined only in a query. To visually create a cross join, start a query in design and select the two desired tables (or two existing queries, or a table and an existing query). In the top lists, select the fields you will need. Then preview the results in the Datasheet View.

In the SQL, the primary formula to create a cross join is:

SELECT field(s) [, field(s)] FROM table1, table2

Start with the SELECT operator and create a list of the desired fields from each table. As mentioned previously, it is a good idea to qualify each field. After the FROM keyword, provide the name of each table involved.

Practical Learning: Creating a Cross Join

  1. On the Ribbon, click Create and click Query Design
  2. In the Show Table dialog box, double-click Registrations and MaritalsStatus
  3. Click Close
  4. In the Registations list, double-click RegistrationID, FirstName, and LastName
  5. In the MaritalsStatus list, double-click MaritalStatus

    Creating a Cross Join

  6. Display the SQL View of the query:
    SELECT Registrations.RegistrationID,
           Registrations.FirstName,
           Registrations.LastName,
           MaritalsStatus.MaritalStatus
    FROM Registrations, MaritalsStatus;
  7. Switch the query to Datasheet View

    Creating a Cross Join

  8. Close the quey without saving it
 
 
 

Inner Joins

An inner join in a query follows the description we saw for inner joins in tables. To visually create an inner join in a query, start the query in design and select the desired tables or existing queries. When you start a query, if you select two tables that have a primary key and a foreign key with the same name and data type, and if no relationship was already established between both tables, a visual link (a line) would automatically be established between both keys. If the relationship is not obvious, you will have to create it yourself. As we saw in Lesson 3: simply drag the primary key from the parent table or query and drop it on the foreign key in the child table or query.

To create an inner join in the SQL, specify the join-type of our formula with the expression INNER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, Persons.GenderID,
       Genders.GenderID AS [Gender ID], Genders.Gender
FROM   Persons INNER JOIN Genders ON Persons.GenderID = Genders.GenderID;

Practical Learning: Introducing Inner Joins in Queries

  1. On the Ribbon, click Create and click Query Design
  2. In the Show Table, double-click Registrations and Employees
  3. Click Close
  4. Drag EmployeeNumber from the Employees list and drop it on ProcessedBy on the Registrations list

    Introducing Inner Joins in Queries

  5. In the Registrations list, double-click RegistrationID and RegistrationDate
  6. In the Employees list, double-click EmployeeName
  7. In the Registrations list, double-click FirstName, LastName, MaritalStatus, and NumberOfChildren

    Introducing Inner Joins in Queries

  8. Display the SQL View of the query:
    SELECT Registrations.RegistrationID,
           Registrations.RegistrationDate,
           Employees.EmployeeName,
           Registrations.FirstName,
           Registrations.LastName,
           Registrations.MaritalSituation,
           Registrations.NumberOfChildren
    FROM Employees INNER JOIN Registrations
    ON Employees.EmployeeNumber = Registrations.ProcessedBy;
  9. Switch to the Datasheet View

    Introducing Inner Joins in Queries

  10. Close the query without saving it

Left Outer Joins

To create a left outer join in the SQL, replace the join-type of our formula with either LEFT JOIN or LEFT OUTER JOIN.

Practical Learning: Using Left Outer Joins

  1. Open the Cruise3 database from the resources that accompany these lessons
  2. On the Ribbon, click Create and click Query Design
  3. In the Show Table, double-click Departments nd Employees
  4. Click Close
  5. Make sure there is a line between the DepartmentCode items.
    In the lists, double-click EmployeeNumber, FirstNasme, LastName, and DepartmentName

    Using Left Outer Joins

  6. Right-click the line between the DepartmentCode items and click Join Properties
  7. In the Join Properties dialog box, click the 2 radio button:

    Introducing Inner Joins in Queries

  8. Click OK

    Using Left Outer Joins

  9. Display the SQL View of the query:
    SELECT Employees.EmployeeNumber,
           Employees.FirstName,
           Employees.LastName,
           Departments.DepartmentName
    FROM   Departments LEFT JOIN Employees
           ON Departments.DepartmentCode = Employees.DepartmentCode;
  10. Switch to the Datasheet View

    Using Left Outer Joins

  11. Close the query without saving it

Right Outer Joins

To create a right outer join in SQL, replace the join-type of our formula with RIGHT JOIN or RIGHT OUTER JOIN.

Practical Learning: Creating a Right Outer Joins in a Query

  1. Open the Lambda Square Apartments1 database created earlier
  2. On the Ribbon, click Create and click Query Design
  3. In the Show Table, double-click Payments and Employees
  4. Click Close
  5. Drag EmployeeNumber from the Employees list and drop it on ProcessedBy on the Payments list

    Creating a Right Outer Joins in a Query

  6. In the lists, double-click ReceiptNumber, PaymentDate, EmployeeName, RegistrationNumber, and PaymentAmount

    Creating a Right Outer Joins in a Query

  7. To preview the results, display the Datasheet View of the query

    Creating a Right Outer Joins in a Query

  8. Notice the number of records: 46.
    Display the Design View of the query
  9. Double-click the line between ProcessedBy and EmployeeNumber
  10. In the Join Properties dialog box, click the 3 radio button

    Introducing Inner Joins in Queries

  11. Click OK
  12. In the lists, double-click ReceiptNumber, PaymentDate, and EmployeeName

    Creating a Right Outer Joins in a Query

  13. Display the SQL View of the query:
    SELECT Payments.ReceiptNumber,
           Payments.PaymentDate,
           Employees.EmployeeName,
           Payments.RegistrationNumber,
           Payments.PaymentAmount
    FROM   Employees RIGHT JOIN Payments
           ON Employees.EmployeeNumber = Payments.ProcessedBy;
  14. Switch to the Datasheet View

    Creating a Right Outer Joins in a Query

  15. Close the query without saving it
  16. Close Microsoft Access
 
 
   
 

Previous Copyright © 2000-2016, FunctionX, Inc. Next