Home

Data Joins

 

Joins

 

Introduction

When studying relationships, we reviewed techniques of making data from one table available to the records of another table. This proved to reduce data duplication and mistakes. Another issue that involves the combination of tables consists of creating records from more than one table and making the result into a single list. This is the basis of data joins.

A data join is a technique of creating a list of records from more that 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. The tables that will be involved in the join
  2. A column that will create the link in each table
  3. A SQL statement that will create the records

These are the steps to follow in real life. Fortunately, Microsoft Access provides all means of visually creating joins without writing a single line of code. In fact, when we studied relationships, we created joins without realizing it, but Microsoft Access took care of everything behind the scenes. To be able to understand how joins are created and maintained, we will work from scratch.

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. Here is an example of such a table:

If needed, you can then create the necessary records for this type of table. Here is an example:

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. Here is an example of a child table that would be joined to the above parent table:

Once again, if necessary, you can add the necessary records to the table. Here is an example:

 

Practical Learning Practical Learning: Introducing Data Joins

  1. Start Microsoft Access and create a blank database named CarInventory
  2. In the Tables section of the Database window, double-click Create Table In Design View and create a table as follows:
     
    Field Name Data Type Caption Field Size Format Other Properties
    CategoryID AutoNumber Category ID     Primary Key
    Category     50    
    DailyRate Number Daily Rate Double Fixed  
    WeeklyRate Number Weekly Rate Double Fixed  
    MonthlyRate Number Monthly Rate Double Fixed  
    WeekendRate Number Weekend Rate Double Fixed  
  3. Save the table as Categories and switch it to Datasheet View
  4. Create a few records as follows:
     
     
  5. Close the table
  6. Double-click Create Table In Design View and create the following fields:
     
    Field Name Data Type Caption Field Size Other Properties
    CarID  AutoNumber Car ID   Primary Key
    TagNumber   Tag Number 50  
    Make     50  
    Model     50  
    CarYear Number Year Integer  
    CategoryID Number Category    
    HasK7Player Yes/No Has K7 Player?    
    HasCDPlayer Yes/No Has CD Player?    
    HasDVDPlayer Yes/No Has DVD Player?    
    Available Yes/No Is Available?   Default Value: 1
  7. Save the table as Cars and close it
  8. In the Database window, click Forms and double-click Create Form In Design View
  9. Save the form as Maintenance
  10. From the Toolbox, click Command Button and click the form. If the Command Button Wizard starts, click Cancel
  11. Change the button's name to cmdCars
  12. Right-click the button and click Build Events then double-click Code Builder
  13. Implement the event as follows:
     
    Private Sub cmdCars_Click()
        Dim conCars As ADODB.Connection
        
        Set conCars = Application.CurrentProject.Connection
        
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, " & _
                        "CarYear, CategoryID, HasK7Player, HasCDPlayer, " & _
                        "HasDVDPlayer, Available) VALUES('HAD-722', " & _
                        "'Hyundai', 'Accent', '2003', 1, 0, 0, 1, 1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                        "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                        "Available) VALUES('CDJ-85F', 'Mercury', 'GrandMarquis', " & _
                        "1998,  4,  0,  0,  1,  0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                        "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                        "Available) VALUES('FGE-920', 'Ford', 'Escape', " & _
                        "2004, 6, 0,  0,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                        "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                        "Available) VALUES('GMM-186', 'Mercury', 'Grand Marquis', " & _
                        "2001, 4,  0,  1,  1,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                        "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                        "Available) VALUES('GHL-22G', 'Lincoln', 'TownCar', " & _
                        "1998,   4,  0,  1,  1,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                        "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                        "Available) VALUES('HHS-382', 'Hyundai', 'Sonata', 2002, " & _
                        "2,  0,  0,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                        "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                        "Available) VALUES('LBN-755', 'Lincoln', 'Navigator', " & _
                        "2000, 6,  1,  1,  1,  0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
    		    "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
    		    "Available) VALUES('FDX-984', 'Kia', 'Sephia', & _
    		    "2002,   2,  0, 1,  1,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, " & _
                                "HasK7Player, HasCDPlayer, HasDVDPlayer, Available) " & _
                                "VALUES('AFS-888', 'Ford', 'SportTrac', 1998, 7,  0, " & _
                                "0,  1,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('CAM-422', 'Chevrolet', 'Metro', " & _
                                "2000,   1,  0,  0,  0, 0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('QFH-608', 'Ford', 'F150', 2001, 7, " & _
                                "0,  0,  1,  0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('DCC-713', 'Chevrolet', 'Camaro', " & _
                                "2001,   3,  1,  0,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('LFT-268', 'Ford', 'Club Wan', " & _
                                "1998,   5,  0,  1,  1,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('PBR-69G', 'Buick', 'Regal', 2000, " & _
                                "4,    0,  1,  1,  0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('DBP-832', 'Buick', 'Park Avenue', " & _
                                "2001,   4,  0,  0,  1,  0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                          "Available) VALUES('FM-685', 'Ford', 'Mustang Convertible', " & _
                                "2002,   3,  1,  0,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('FAX-48T', 'Mecury', 'Villager', " & _
                                "1999,   5,  1,  0,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('DPM-42', 'Pontiac', 'Mountana', " & _
                                "2002,   5,  0,  1,  1,  0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('AFW-928', 'Ford', " & _
                                "'Windstar Minivan GL',    2001,   5,  0,  0,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('UFX-963', 'Cadillac', " & _
                                "'Sedan de Ville', 1998,   4,  1,  1,  1,  0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('KCR-656', 'Chevrolet', 'Blazer', " & _
                                "2001,   6,  0,  0,  1,  0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('LLT-358', 'Lincoln', 'City Car', " & _
                                "2004,   4,  0,  1,  1,  0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('RBL-618', 'Buick', 'LeSabre', " & _
                                "2002,   4,  0,  1,  1,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('WFV-688', 'Ford', 'E350', 2000, " & _
                                "8,  0,  0,  0,  0)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('GCV-557', 'Chevrolet', 'Camaro', " & _
                                "1999, 3,  0,  1,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('TPG-905', 'Pontiac', 'Grand Am', " & _
                                "2002,   2,  0,  0,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('JYW-682', 'Jeep', 'Wrangler', " & _
                                "2003,   6,  1,  0,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('DFR-214', 'Ford', 'Ranger', 2000, " & _
                                "7,  0,  0,  1,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('RKR-670', 'Kia', 'Rio', 2002, " & _
                                "1,  1,  0,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('YJC-498', 'Ford', 'Escort', 1996, " & _
                                "1,  0,  0,  0,  1)"
    
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('QDC-922', 'Dodge', 'Caravan', " & _
                                "2002,   5,  1,  0,  0,  0)"
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('DSS-374', 'Hyundai', 'Accent', " & _
                                "1996,   1,  0,  0,  0,  1)"
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('SCC-262', 'Chrysler', 'Concorde', " & _
                                "2002,   4,  0,  1,  1,  1)"
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('BDI-588', 'Dodge', 'Intrepid', " & _
                                "2004,   2,  1,  0,  0,  0)"
        conCars.Execute "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                                "CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, " & _
                                "Available) VALUES('MCM-952', 'Jaguar', 'S-Type', " & _
                                "1999,   4,  0,  1,  1,  1)"
        conCars.Close
        Set conCars = Nothing
    End Sub
  14. Return to the form and switch it to Form View
  15. Click the button
  16. Save and close the form

 

 

Join Creation

Equipped with the necessary tables and their columns, you can create the join(s). To do this, on the main menu of Microsoft Access, you can click Insert -> Query and, in the New Query dialog box, click Design View. You would be presented with the Show Table dialog box. You can select a table and click Add. Because the foundation of a join lies on various tables, you should add at least two tables. After adding the tables, click Close. Here is an example:

If a relationship was already established between the tables, a joining line would show it. Even if no relationship existed already, after selecting the tables, if Microsoft Access finds a common used by a primary key of the parent table and a foreign key in the child table, it would create a linking line between both tables .

As we will see, you can visually create a join in the Select Query window or you can write code to do it. To write code, you can right-click the window and click SQL View.

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

SELECT WhatColumn(s)
FROM ChildTable
TypeOfJoin ParentTable
ON Condition

The ChildTable factor specifies the table that holds the records that will be retrieved. It can be represented as follows:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin ParentTable
ON Condition

The ParentTable factor specifies the table that holds the column with the primary key that will control what records, related to the child table, that will display. This factor would be represented as follows:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

The Condition factor is a logical expression used to validate the records that will be isolated. To create the condition, you should assign the primary key column of the parent table to the foreign key column of the child table. Because, and if, both columns likely have the same name, to distinguish them, their names should be qualified. This would be done as follows:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

The WhatColumn(s) factor 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
TypeOfJoin 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
TypeOfJoin 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
TypeOfJoin 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
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

Types of 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 part 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 as it is applied to a query, the SQL supports two types of joins.

Inner Joins

Imagine you have two tables that can be linked through one's primary key and another's foreign key.

Notice that some records in the Persons table don't have an entry. When creating a query of records of the Persons table, if you want your list to include only records that have an entry, you can create it as inner join. To do this, you would specify the TypeOfJoin factor of our formula with the expression INNER JOIN. Here is an example:

SELECT Persons.LastName, Persons.FirstName, Persons.GenderID,
             Genders.GenderID, Genders.Gender
FROM Persons
INNER JOIN Genders
ON Persons.GenderID = Genders.GenderID

This would produce:

By default, when creating a new query, if a relationship was already established between both tables, the query is made an inner join. If there was no relationship explicitly established between both tables, you would have to appropriately edit the SQL statement. In this case, you have two options. You can drag the primary key from the parent table and drop it on the foreign key in the child table:

As another option, you can edit the SQL statement manually to make it an inner join.

We mentioned earlier that you could include all columns in the query. In our result, since we are more interested in the gender of each Persons record, we would not need the GenderID column from the Genders table.

Practical Learning Practical Learning: Using Inner Joins

  1. On the Database window, click the Queries button
  2. On the main menu, click Insert -> Query
  3. In the New Query dialog box, double-click Design View
  4. On the Show Table dialog box, double-click Categories and Cars
  5. Click Close
  6. In the Categories list, double-click Category
  7. In the Cars list, double-click Make, Model, CarYear, and Available
  8. Save the query as CarsInventory
     
  9. Right-click somewhere in the window and click SQL View
     
  10. Notice the INNER JOIN expression in the statement.
    To preview the result, right-click the title bar of the window and click Datasheet View
     
  11. After viewing the result, right-click the title bar of the window and click Query Design

Outer Joins

 

Introduction

Instead of showing only records that have entries in the child table, you may want your query to include all records, including those that are null. To get this result, you would create an outer join. You have three options.

Left Outer Joins

A left outer join produces all records of the child table, also called the right table. The records of the child table that don't have an entry in the foreign key column are marked as NULL.

To create a left outer join, you can replace the TypeOfJoin factor of our formula with LEFT JOIN or LEFT OUTER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
             Genders.GenderID, Genders.Gender
FROM Persons
LEFT OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID

This would produce:

Notice that the result includes all records of the Persons (also called the right) table and the records that don't have an entry in the GenderID column of the Persons (the right) table are left empty.

To create a left outer join in the Data In Table window, after establishing a relationship between both tables, you can right-click the line that joins them and click Join Properties:

In the Join Properties dialog box, read and click the 2 radio button and click OK:

This would change the join into a left join.

Practical Learning Practical Learning: Using Left Outer Joins

  1. Right-click the title bar of the window and click SQL VIEW
  2. Change the INNER JOIN expression to LEFT OUTER JOIN
     
  3. Right-click the title bar of the window and click Query Design
     
  4. To see the result, right-click the title bar of the window and click Datasheet View
  5. Notice that the result is the same
  6. Right-click the title bar of the window and click Query Design
  7. To add a few columns, in the Cars list, drag HasCDPlayer and drop it on top of Available in the lower section
  8. In the Categories list, click DailyRate, press and hold Ctrl
  9. Click WeekendRate and release Ctrl
  10. Drag the selected columns and drop them on top of Available in the lower section
     
  11. Right-click somewhere in the window and click Datasheet View 
     
  12. Right-click the title bar of the window and click SQL View

 

Right Outer Joins

A right outer join considers all records from the parent table and finds a matching record in the child table. To do this, it starts with the first record of the parent table (in this case the Genders table) and shows each record of the child table (in this case the Persons table) that has a corresponding entry. This means that, in our example, a right outer join would first create a list of the Persons records that have a 1 (Female) value for the GenderID column. After the first record, the right outer joins moves to the second record, and so on, each time listing the records of the child table that have a corresponding entry for the primary key of the parent table.

To create a right outer join, you can replace the TypeOfJoin factor of our formula with RIGHT OUTER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
             Genders.GenderID, Genders.Gender
FROM Persons
RIGHT OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID

This would produce:

Notice that the query result starts with the first record of the parent table, also called the left table (in this case the Genders table), and lists the records of the child table, also called the right table (in this case the Persons table), that have the entry corresponding to that first record. Then it moves to the next GenderID value.

To create a right outer join in the Select Query window, after establishing a relationship between both tables, you can right-click the line that joins them and click Join Properties. Then, in the Join Properties dialog box, click the 3 radio button:

And click OK

Practical Learning Practical Learning: Using Right Outer Joins

  1. Replace the word LEFT with RIGHT
     
  2. Right-click the title bar and click Query Design
     
  3. Right-click the line between the tables and click Join Properties
     
  4. Click OK
  5. Right-click somewhere in the window and click Datasheet View
  6. Right-click the title bar of the window and click Query Design
  7. Double-click the line between both lists
  8. In the Join Properties dialog box, click the 2 radio button and click OK

Joins and Data Analysis

 

Introduction

As demonstrated so far and in previous lessons, the main reason for creating queries is to isolate records. This is done using conditions and criteria. Joins enhance this capability because they allow you to consider records from different tables and include them in a common SQL statement.

In the 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 useful or restrictive, you can pose your own conditions that should be respected to isolate records like a funnel. As done in previous sections about queries, to include a criterion in a SELECT statement, you can create a WHERE clause.

Using Criteria

To create a criterion in a query, first select a column to display it in the lower section. Just as reviewed in the previous lessons when creating a query, to specify a criterion, in the Criteria box corresponding to the column, type the condition using any of the operators we reviewed in previous lessons. Here is an example:

After specifying the criteria, the database engine would automatically include it in the SQL statement:

And you can view the result:

Notice that only the Persons records with a Female entry display.

Practical Learning Practical Learning: Using Criteria With Joins

  1. Click Criteria field that corresponds to the Available column and type =True
  2. Click the check box of the same column to clear it
  3. Add the other columns as you see fit
     
  4. Right-click somewhere in the window and click SQL View
     
  5. To view the result, right-click the title bar of the window and click Datasheet View
  6. Save and close the query

 


Previous Copyright Yevol, 2007 Next