Home

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:

Joins and Record Nullity

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:

Joins and Record Nullity

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

    Data Joins and Record Nullity

  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

    Joins and Record Nullity

  11. Switch the query to Datasheet View

    Joins and Record Nullity

  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

    Joins and Record Nullity

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

    Dealing with Data Joins and Record Nullity

  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

    Joins and Record Nullity

  41. Switch the query to Datasheet View

    Joins and Record Nullity

  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

    Joins and Record Nullity

  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] & ")"

    Joins and Record Nullity

  53. Switch the query to Datasheet View

    Joins and Record Nullity

  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

    Joins and Record Nullity

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

    Introducing Inner Joins in Queries

    Introducing Inner Joins in Queries

  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:

    Introducing Inner Joins in Queries

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

    Using a Shared Field to Join Tables

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

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