Data Joins and Relationships

Options on Data Joins

Data Joins and Record Nullity

Sometimes you will have a table where a field is missing some records. Here is an example of a list of employees where some records don't specify the department to which the employee belongs:

SELECT Employees.EmployeeNumber AS [Employee #],
       Employees.FirstName,
       Employees.LastName,
       Employees.DepartmentCode AS Department
FROM Employees;

This would produce:

If you create an inner join query of two tables that have a relationship and some records of the child table are missing some values in the foreign key column, the query would produce only the records that have a value. Here is an example:

SELECT Employees.EmployeeNumber AS [Employee #],
       Employees.FirstName,
       Employees.LastName,
       Departments.DepartmentName AS Department
FROM   Departments INNER JOIN Employees
       ON Departments.DepartmentCode = Employees.DepartmentCode;

This would produce:

Notice that the query produces fewer records. As it happens, database engines don't like null records especially on shared fields, fields that are involved in relationships. To make sure that all records of a child table are produced by a query, produce a default value for records whose values are missing. If the relationship is using a simple integer, create a record with the 0 value. If the relationship is using a string, you can create a record with an 'N/A' value. If the records are processed by a computer, you can create a record with a generic/random value such as 00000 or something like that.

Practical Learning: Dealing with Data Joins and Record Nullity

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Altair Realtors3 database
3. On the Ribbon, click Create and click Query Design
4. In the Show Table dialog box, double-click Properties and click Close
5. In the Properties list, double-click PropertyNumber, City, and State
6. Switch the query to Datasheet View:

   

7. Notice the number of records: 48.
   Switch the query to Design View
8. Right-click an empty area in the top side of the window and click Show Table
9. In the Show Table dialog box, double-click PropertyTypes and click Close
10. In PropertyTypes list, double-click PropertyType

    

11. Switch the query to Datasheet View

    

12. Notice the number of records this time: 44.
    Display the SQL View of the query and change its code as follows:

    SELECT Properties.PropertyNumber,
           Properties.City,
           Properties.State,
           Conditions.Condition
    FROM   Properties INNER JOIN Conditions
           ON Properties.Condition = Conditions.Condition;

13. Switch the query to Datasheet View

    

14. Notice the number of records: 38.
    Close the query without saving it
15. In the Navigation Pane, double-click the PropertyTypes form
16. Create a new record as follows:
     

    PropertyType	Description
    Unknown	The property type is not available or it is not clear.
    Illustrations

17. Close the PropertyTypes table
18. In the Navigation Pane, double-click the Conditions form
19. Create a new record as follows:
     

    PropertyType	Description
    Unknown	The current condition or appearance (interior and/or exterior) of the property has not been checked, was not assessed, or is not clearly known.

20. Close the Conditions table
21. In the Navigation Pane, right-click the Properties table and click Design View
22. In the top side of the window, click PropertyType
23. In the bottom side, click Default Value and type Unknown
24. In the top side of the window, click Condition
25. In the bottom side, click Default Value and type Unknown
26. Save the table and switch it to the Datasheet View
27. In the PropertyType column, for each empty cell, select Unknown
28. In the Condition column, for each empty cell, select Unknown
29. Notice that there is a default value for both the PropertyType and the Condition columns
30. By setting only the indicated values, create new records as follows:
     

    Property #	Property Type	City	State	ZIP Code	Bedrooms	Bathrooms	Finished Basement	Indoor Garage	Condition	Market Value
    476005	Single Family	Hanover	PA	17331	3	2.50	Checked			425790
    152466		Martinsburg	WV		1	1.00				135670
    427048	Condominium	Alexandria	VA		3	2.00			Needs Repair	622845
    297427		Matinsburg							Excellent
    729336		Alexandria	VA		3	2.50			Needs Repair	750000
    300618		Harrisburg	PA	17109	5	3.50	Checked	Checked	Good Shape	515885

    

31. Close the table
32. On the Ribbon, click Create and click Query Design
33. In the Show Table dialog box, double-click Properties and click Close
34. In the Properties list, double-click PropertyNumber, City, and State
35. Switch the query to Datasheet View
36. Notice the number of records: 51.
    Switch the query to Design View
37. Right-click an empty area in the top side of the window and click Show Table
38. In the Show Table dialog box, double-click PropertyTypes and click Close
39. In the PropertyTypes list, drag PropertyType and drop it on City in the bottom side
40. In the Properties list, double-click Bedrooms and Bathrooms

    

41. Switch the query to Datasheet View

    

42. Notice the number of records this time: 51.
    Close the query without saving it
43. Open the Lambda Square Apartments1 database created and used earlier
44. On the Ribbon, click Create and click Query Design
45. In the Show Table dialog box, double-click Payments and click Close
46. In the Payments list, double-click ReceiptNumber, PaymentDate, ProcessedBy, RegistrationNumber, and PaymentAmount
47. Switch the query to Datasheet View

    

48. Notice the number of records: 63.
    Switch the query to Design View
49. Right-click an empty area in the window and click Show Table...
50. In the Show Table dialog box, double-click Employees and click Close
51. Drag EmployeeNumber and drop it on ProcessedBy
52. In the bottom side of the window, replace ProcessedBy with

    Employee: [EmployeeNumber] & " - " & [EmployeeName] & " (" & [Title] & ")"

    

53. Switch the query to Datasheet View

    

54. Notice the number of records: 46.
    Close the query without saving it
55. In the Navigation Pane, double-click the Employeees table and create a new record as follows (this is for transactions made at the bank such as a tenant who deposits rent money directly at the bank):
     

    EmployeeNumber	FirstName	LastName	Title
    00000	Automatic	Processing	Electronic Transaction

56. Close the Employees table
57. In the Navigation Pane, right-click the Payments table and click Design View
58. In the top side of the window, click ProcessedBy
59. In the bottom side, click Default Value and type "00000"
60. Save and close the table
61. Open the Payments table
62. In the ProcessedBy column, in every empty cell, type 00000
63. Close the Payments table
64. On the Ribbon, click Create and click Query Design
65. In the Show Table dialog box, double-click Payments and Employees
66. Click Close
67. Drag EmployeeNumber and drop it on ProcessedBy
68. In the Payments list, double-click ReceiptNumber and PaymentDate
69. Press Tab and type

    Employee: [EmployeeNumber] & " - " & [EmployeeName] & " (" & [Title] & ")"

70. In the Paynments list, double-click RegistrationNumber and PaymentAmount
71. Switch the query to Datasheet View

    

72. Notice the number of records: 63.
    Close the query without saving it

Joining More Than Two Tables

So far, our join statements involved only two tables. Actually, you can use more tables than that. The basic formula to join three tables is:

SELECT field-name(s) FROM first-table
first-join-type second-table ON condition1
second-join-type third-table ON condition2

You start the expression by joining the first to the second tables, which means that both tables should share a column in a primary key-foreign key type of relationship. In the same way, you can create the second join. Of course, the second and the third tables should have a common column. In most cases, there should be a column that all three tables share. Most of the time, the relationship starts with a primary key column from the parent table. That column is then represented as a foreign key in the other two tables.

Practical Learning: Joining More Than Two Tables

1. On the Ribbon, click Create and click Form Design
2. In the Property Sheet, click the All tab.
   Click Record Source and click its ellipsis button
3. In the Show Table, double-click Employees, Registrations, and Apartments
4. Click Close
5. Drag EmployeeNumber from the Employees list and drop it on ProcessedBy on the Registrations list
6. Drag ApartmentCode from the Apartments list and drop it on PropNbr on the Registrations list

   

   

7. In the Registrations list, double-click RegistrationID and RegistrationDate
8. Press Tab and type

   Employee: [EmployeeNumber] & " - " & [EmployeeName] & " (" & [Title] & ")"

9. In the Registrations list, double-click FirstName, LastName, MaritalStatus, and NumberOfChildren
10. Press Tab and type:

    Apartment: "Apart # " & [UnitNumber] & ", " & [Bedrooms] & " bedroom(s), " & [Bathrooms] & " bathroom(s), Rate: " & [MonthlyRate] & "/month"

11. Display the SQL View of the query:

    SELECT Registrations.RegistrationID AS [Regist #],
           Registrations.RegistrationDate AS [Regist Date],
           [EmployeeNumber] & " - " & [EmployeeName] & " (" & [Title] & ")" AS Employee,
           Registrations.FirstName,
           Registrations.LastName,
           Registrations.MaritalSituation AS Status,
           Registrations.NumberOfChildren AS Children,
           "Apart # " & [UnitNumber] & ", " & [Bedrooms] & " bedroom(s), " & [Bathrooms] & " bathroom(s), Rate: " & [MonthlyRate] & "/month" AS Apartment
    FROM   Apartments INNER JOIN (Employees INNER JOIN Registrations
           ON Employees.EmployeeNumber = Registrations.ProcessedBy)
           ON Apartments.ApartmentCode = Registrations.PropNbr;

12. Close the Query Builder
13. When asked whether you want to save the changes, click Yes
14. Save the form as Rent Allocations
15. Double-click the button at the intersection of the rulers
16. In the Property Sheet, change the following characteristics:
    Caption: Lambda Square Apartments - Rent Allocation
    Default Value: Continuous Form
    Auto Center: Yes
    Navigation Buttons: No
17. On the Ribbon, click Design
18. In the Tools section, click Add Existing Fields
19. In the Field List, click Apartment, press and hold Shift, click Regist #, and release Shift
20. Drag the selection to the form
21. On the Ribbon, click Arrange and click Tabular
22. Drag the selection to the left
23. Press Ctrl + A to select all controls
24. On the Ribbon, click Remove Layout
25. Complete the design of the form. Here is an example:

    

26. Save and close the form

Data Joins and Records Analyses

Sorting Records

In the data joins we have created so far, we considered all records and let the database engine list them using only the rules of joins built-in the SQL. To make such a list more restrictive, you can apply some conditions by which to isolate some records. You can visually create the filters in the Design View of a query or in a similar window. You can also create a filter in SQL. As done in previous lessons, to include a criterion in a SELECT statement, you can create a WHERE clause.

Practical Learning: Introducing Joins and Data Analysis

1. Open the Monson University1 database used earlier
2. To start a query, on the Ribbon, click Create and click Query Design
3. On  the Show Table dialog box, click Close
4. Right-click the top side of the window and click SQL View
5. Change the statement as follows:

   SELECT EmployeeNumber AS [Employee #],
          FirstName      AS [First Name],
          LastName       AS [Last Name],
          Title,
          DepartmentName AS Department
   FROM   Employees, Departments
   WHERE  Employees.DepartmentCode = Departments.DepartmentCode
   ORDER  BY LastName;

6. Preview the results in the Datasheet View
7. If necessary, on the Ribbon, click Home.
   Change the following characteristics:
   Font Name: Californian FB (if you don't have that font, select Times New Roman)
   Font Color: Blue, Accent 1, Lighter 80% (Theme Colors: 5th column, 2nd row)
   Background Color: More Colors: Red: 195, Green: 95, Blue: 15
   Gridlines: Horizontal
   Alternate Row Color: More Colors: Red: 128, Green: 0, Blue: 0

   

8. Close the query without saving it
9. Close Microsoft Access