Home

Introduction to Data Joins

A Shared Field to Join Tables

Using a Shared Field to Join Two Tables

One of the most important features of a relational database consists of combining records from various tables to get a single list. The SQL provides two main options: Applying a condition on a common field or creating a join.

The primary way to join two tables to create a common list that combines their records is to match the records they have in common. Before doing this, the lists must have a field used as the primary key on one table and a foreign key on the other table. The formula to follow is:

SELECT Field(s) [, Field(s)] FROM Table1, Table2
WHERE Condition

You use a SELECT statement to select fields from one or all tables, then you use a WHERE condition to specify how the records will be matched. The condition should be in the form:

primary-key = foreign-key

Because the primary key and the foreign key may have the same names, you should qualify their names.

Practical LearningPractical Learning: Using a Shared Field to Join Tables

  1. Start Microsoft Access and open the Monson University1 database from Lesson 25
  2. To start a query, on the Ribbon, click Create and click Query Design
  3. In  the Show Table dialog box, click Employees, click Add, and click Close
  4. In the Students list, double-click EmployeeNumber, FirstName, LastName, and Title
  5. Preview the results in the Datasheet View
  6. 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: Red: 195, Green: 95, Blue: 15
    Gridlines: Horizontal
    Alternate Row Color: More Colors: More Colors: Red: 128, Green: 0, Blue: 0

    Using a Shared Field to Join Tables

  7. Display the SQL View of the query
  8. To join the records from another table, change the statement as follows:
    SELECT Employees.EmployeeNumber AS [Employee #],
           Employees.FirstName AS [First Name],
           Employees.LastName AS [Last Name],
           Employees.Title,
           Departments.DepartmentName AS Department
    FROM   Employees, Departments
    WHERE  Employees.DepartmentCode = Departments.DepartmentCode;
  9. Display the Datasheet View of the query

    Using a Shared Field to Join Tables

  10. Close the query
  11. When asked whether you want to save, click Yes
  12. Set the name to Employees Summary and click OK
  13. Open the FunDS3 database from Lesson 33
  14. On the Ribbon, click Create and click Form Design
  15. If the Property Sheet is not available, double-click the button at the intersection of the rulers.
    Click the All tab
  16. Click Record Source and click its ellipsis button
  17. In the Show Table dialog box, double-click ShoppingSessions and Employees
  18. Click Close
  19. In the ShoppingSessions list, double-click ReceiptNumber
  20. Press Tab and type
    Clerk: [EmployeeNumber] & ": " & [EmployeeName]
  21. In the ShoppingSessions list, double-click ShoppingDate, ShoppingTime, SalesTotal, and AmountTendered

    Using a Shared Field to Join Tables

  22. Display the SQL View of the query and change its code as follows:
    SELECT ShoppingSessions.ReceiptNumber,
           [EmployeeNumber] & ": ": & [EmployeeName] AS Clerk,
           ShoppingSessions.ShoppingDate,
           ShoppingSessions.ShoppingTime,
           ShoppingSessions.SalesTotal,
           ShoppingSessions.AmountTendered
    FROM   ShoppingSessions, Employees
    WHERE  ShoppingSessions.StaffMember = Employees.EmployeeNumber;
  23. Close the Query Builder
  24. When asked whether you want to save the changes, click Yes
  25. Save the form as Customers Purchases
  26. In the Property Sheet, change the following characteristics:
    Caption: Fun Department Store - Customers Purchases
    Default Value: Continuous Form
    Auto Center: Yes
  27. On the Ribbon, click Design
  28. In the Tools section, click Add Existing Fields
  29. In the Field List, click ReceiptNumber, press and hold Shift, click AmountTenderd, and release Shift
  30. Drag the selection to the form
  31. In the Controls section of the Ribbon, click Text Box and click below the other controls on the form
  32. Change the caption of the accompanying label to Change
  33. Click the text box and, in the All tab of the Property Sheet, change the following characteristics:
    Name: txtChange
    Control Source: =Nz([AmountTendered])-Nz([SalesTotal])
    Format: Fixed
  34. On the Ribbon, click Arrange and click Tabular
  35. Drag the selection to the left
  36. Press Ctrl + A to select all controls
  37. On the Ribbon, click Remove Layout
  38. Complete the design of the form. Here is an example:

    Using a Shared Field to Join Two Tables

  39. To preview the results, switch the form to Form View

    Using a Shared Field to Join Two Tables

  40. Save and close the form

Creating a Join Using Various Shared Fields

If a table shared fields with more than one other table, you can used their shared fields to join them. The formula to follow is:

SELECT Field(s) [, Field(s)] FROM Table1, Table2
WHERE condition1 AND condition2

You use the logical conjunction operator AND to concatenate the conditions.

Practical LearningPractical Learning: Creating a Join Using Various Shared Fields

  1. Open the Monson University1 database used earlier
  2. To start a query, on the Ribbon, click Create and click Query Design
  3. In  the Show Table dialog box, click Close
  4. Display the SQL View of the query
  5. To join the records from two other tables, change the statement as follows:
    SELECT StudentNumber AS [Student #],
           FirstName AS [First Name],
           LastName AS [Last Name],
           DateOfBirth AS DOB,
           Year(Date()) - Year(DateOfBirth) AS Age,
           Gender,
           Major,
           Minor
    FROM Students, Majors, Minors
    WHERE Students.MajorID = Majors.MajorID AND Students.MinorID = Minors.MinorID;
  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: Red: 195, Green: 95, Blue: 15
    Alternate Row Color: More Colors: More Colors: Red: 128, Green: 0, Blue: 0

    Using a Shared Field to Join Tables

  8. Close the query
  9. When asked whether you want to save, click Yes
  10. Set the name to Undergraduate Students and click OK
 
 
 

Fundamentals of Data Joins

Introduction

A data join is a technique of creating a list of records from more than one table, using all columns from all tables involved, or selecting only the desired columns from one or all of the tables involved. This means that a data join is essentially created in three steps:

  1. Selecting the tables that will be involved in the join
  2. Selecting or designating the column that will participate in the join
  3. Linking the shaed fields or writing a SQL statement that will create the join

The Tables of a Join

Before creating a join, you must have the tables that would be involved. The tables are created using the techniques we have seen in previous lessons. It is also important to create a primary key for each table. The parent table would usually need only this primary key that would be used to "link" it to a child table. If needed, you can then create the necessary records for the table.

When creating the child table, remember to create a column that would serve as the link with the parent table. By a (good) habit as we saw when studying relationships, the name and the data type of this column are the same as the primary key of the parent table.

Practical LearningPractical Learning: Creating the Tables of a Data Join

  1. On the Ribbon, click File and click New
  2. Click Blank Desktop Database
  3. Set the File Name to Chemistry3 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 Chemistry3
  10. To start a new table, on the Ribbon, click Create and click Table Design
  11. Type Phase as the first field name and press Tab
  12. On the Ribbon, click the Primary Key button Primary Key
  13. Commplete the fields as follows:
     
    Field Name Data Type Field Size
    Phase   20
    Description Long Text  
  14. Switch the table to Datasheet View
  15. When asked whether you want to save, click Yes
  16. Set the name to Phases and click OK
  17. Create the records as follows:
     
    Field Name Description
    Solid Solid is the stiff and hard state that characterizes matter. It means that the material cannot physically be bent. It cannot let gas or liquid through.
    Gas Gas is a chemical that specifies that a matter is primarily made of one type of molecule made of one or more atoms. While the components (molecules) of a solid are tight (or tightly bound), those of a gas can be separate but still maintaining a gaseous aspect.
  18. Close the table
  19. To start a new table, on the Ribbon, click Create and click Table Design
  20. Type AtomicNumber as the first field name and press Tab
  21. On the Ribbon, click the Primary Key button Primary Key
  22. Commplete the fields as follows:
     
    Field Name Data Type Field Size Caption
    AtomicNumber Number   Atomic #
    Symbol   5  
    ElementName   20 Element
    AtomicWeight Number   Atomic Weight
    Phase   20  
  23. Switch the table to Datasheet View
  24. When asked whether you want to save, click Yes
  25. Set the name to Elements and click OK
  26. Create the records as follows:
     
    Atomic # Symbol Element Weight Phase
    1 H Hydrogen 1.00794 Gas
    2 He Helyum 4.002602 Gas
    3 Li Lithium 6.941 Solid
    4 Be Beryllium 9.0121831 Solid
    5 B Boron 10.811 Solid
    6 C Carbon 12.0107 Solid
    7 N Nitrogen 14.007 Gas
    8 O Oxygen 15.999 Gas
    9 F Fluorine 18.9984 Gas
    10 Ne Neon 20.1797 Gas
    11 Na Sodium 22.98977 Solid
    12 Mg Magnesium 24.305 Solid
    13 Al Aluminium 26.98154 Solid
    14 Si Silicon 28.085 Solid
    15 P Phosphorus 30.97376 Solid
    16 S Sulfur 32.06 Solid
    17 Cl Chlorine 35.45 Gas
    18 Ar Argon 39.948 Gas
    19 K Potassium 39.0983 Solid
    20 Ca Calcium 40.078 Solid
  27. Close the table

Visually Creating a Data Join

After creating the necessary tables and their columns, you can create the data join. You can do this in the Relationships window or when building a query. A data join created in the Relationships window will be part of the database.

To create a data join in the Relationship window, first create or start a data relationship as we saw in Lesson 3. Then:

  • If you are just creating the relationship, click Join Type
  • If the relationship was ceated already:
    • Right-click its line in the Relationships window and click Edit Relationships. Then click Join Type
    • Double-click its line in the Relationships window and click Edit Relationships. Then click Join Type

Any of these actions would display the Join Properties dialog box:

Join Properties

Creating a Data Join in the SQL

In the SQL, the basic formula to create a join is:

SELECT field-name(s)
FROM child-table
join-type parent-table
ON condition

The child-table specifies the table that holds the records to be retrieved. It can be represented as follows:

SELECT field-name(s)
FROM Persons
join-type parent-table
ON condition

The parent-table specifies the table that holds the column with the primary key that will control what records, related to the child table, must be used. This would be represented as follows:

SELECT field-name(s)
FROM Persons
join-type Genders
ON condition

The condition is a logical expression used to validate the records that will be isolated. The condition can be created using the following formula:

Table1Column Operator Table2Column

To create the condition, you start with the ON keyword. You can assign the primary key column of the parent table to the foreign key column of the child table. Because both columns likely have the same name, to distinguish them, their names should be qualified. This would be done as follows:

SELECT field-name(s)
FROM Persons
join-type Genders
ON Persons.GenderID = Genders.GenderID

Although we used the assignment operator "=", another operator, such as LIKE, can also be used, as long as it can be used to assign one column to another. Here is an example:

SELECT field-name(s)
FROM Persons
join-type Genders
ON Persons.GenderID LIKE Genders.GenderID

The field-name(s) of our formula allows you to make a list of the columns you want to include in your statement. As you should be aware, you can include all columns by using the * operator. Here is an example:

SELECT *
FROM Persons
join-type Genders
ON Persons.GenderID = Genders.GenderID

In this case, all columns from all tables would be included in the result. Instead of all columns, you may want a restricted list. In this case, create the list after the SELECT keyword separating them with commas. You can use the name of a column normally if that name is not duplicated in more than one column. Here is an example:

SELECT LastName, FirstName, Gender
FROM Persons
join-type Genders
ON Persons.GenderID = Genders.GenderID

If the same name of a column is found in more than one table, as is the case for a primary-foreign key combination, you should qualify the name. Here is an example:

SELECT LastName, FirstName, Persons.GenderID,
       Genders.GenderID, Gender
FROM Persons
join-type Genders
ON Persons.GenderID = Genders.GenderID

In fact, to make your code easier to read, you should qualify the name of each column of your SELECT statement. Here is an example:

SELECT Persons.LastName, Persons.FirstName, Persons.GenderID,
       Genders.GenderID, Genders.Gender
FROM Persons
join-type Genders
ON Persons.GenderID = Genders.GenderID

Inner Joins

Introduction

When studying data relationships, we saw the role of the primary and foreign keys in maintaining the exchange of information between two tables. This technique of linking tables plays a major role when creating a join. It allows you to decide whether you want to include all records or only isolate some of them. To respect the direction of a relationship between two tables, the SQL supports three types of joins.

Practical Learning: Creating Data Relationships

  1. On the Ribbon, click Database Tools and click Relationships
  2. In the Show Table dialog box, double-click Phases and Elements
  3. Click Close
  4. Drag Phasse from the Phases list and drop it on Phase from the Elements list

    Creating Data Relationships

  5. As seen in Lesson 25, click the Enforce Referential Integrity check box
  6. Click the two check boxes below it

    Creating Data Relationships

Creating an Inner Join

Imagine you have two tables that can be linked through one's primary key and another's foreign key. You may have some records in the child table that don't have a value for the primary key of the parent table. Such records may have an empty field. When creating a relationship or a query, if you want the result to include only the records that have a value, you can create the relationship known as an inner join.

To visually create an inner join in the Relationships window, simply drag the primary key from the parent table and drop it on the foreign key in the child table as seen in Lesson 3.

To destroy a join between two tables, if you are working in the Relationships window, right-click the line that joins the tables and click Delete.

Practical LearningPractical Learning: Creating an Inner Join

  1. In the Edit Relationships dialog box, click Join Type

    Join Properties

  2. In the Join Properties dialog box, make sure the 1 radio button is clicked.
    In the Join Properties dialog box, click OK

    Creating Data Relationships

  3. Close the Relationships window
  4. Read the message and click Yes
  5. Close Microsoft Access
 
 
   
 

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