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

   

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

   

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

   

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

   

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

   

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

   

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:

   

8. Click OK

   

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

    

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

   

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

   

7. To preview the results, display the Datasheet View of the 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

    

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

    

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

    

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