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

   

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

   

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

    

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:

    

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

    

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

   

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

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

   

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

   

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 Learning: Creating an Inner Join

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

   

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

   

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

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