Home

Data Joins

 

Data Junctions

 

Introduction

Data relationships provide the techniques of making data from one table available to the records of another table. This proves 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.

Practical LearningPractical Learning: Introducing Relationships

  1. Start Microsoft Access
  2. Create a blank database named CarInventory1
  3. Close the default table without saving it
  4. On the Ribbon, click Create
  5. In the Forms section, click Form Design
  6. In the Controls section of the Ribbon, click the Button and click the Detail section of the form. If the Button Wizard starts, click Cancel
  7. On the form, double-click the button to access its Properties window.
    Change its Name to cmdCreateTables
  8. Change its Caption to Create Tables
  9. Right-click the Create Tables button and click Build Event...
  10. In the Choose builder dialog box, double-click Code Builder
  11. Implement the event as follows:
     
    Private Sub cmdCreateTables_Click()
        DoCmd.RunSQL "CREATE TABLE Categories(" & _
                     "CategoryID AutoIncrement(1001, 1) " & _
                     "           Primary Key Not Null, " & _
                     "Category VarChar(50)," & _
                     "DailyRate Double, WeeklyRate Double, " & _
                     "MonthlyRate Double, WeekendRate Double);"
        MsgBox "A table named Categories has been created."
        
        DoCmd.RunSQL "INSERT INTO Categories(Category, " & _
                     "DailyRate, WeeklyRate, MonthlyRate, WeekendRate) " & _
                     "VALUES('Economy', 34.95, 30.85, 28.95, 24.95);"
        DoCmd.RunSQL "INSERT INTO Categories(Category, " & _
                     "DailyRate, WeeklyRate, MonthlyRate, WeekendRate) " & _
                     "VALUES('Compact', 39.95, 35.75, 32.95, 29.95);"
        DoCmd.RunSQL "INSERT INTO Categories(Category, " & _
                     "DailyRate, WeeklyRate, MonthlyRate, WeekendRate) " & _
                     "VALUES('Standard', 45.95, 40.75, 36.95, 32.95);"
        DoCmd.RunSQL "INSERT INTO Categories(Category, " & _
                     "DailyRate, WeeklyRate, MonthlyRate, WeekendRate) " & _
                     "VALUES('Full Size', 49.95, 45.75, 40.95, 35.95);"
        DoCmd.RunSQL "INSERT INTO Categories(Category, " & _
                     "DailyRate, WeeklyRate, MonthlyRate, WeekendRate) " & _
                     "VALUES('Mini Van', 55.95, 50.75, 45.95, 38.95);"
        DoCmd.RunSQL "INSERT INTO Categories(Category, " & _
                     "DailyRate, WeeklyRate, MonthlyRate, WeekendRate) " & _
                     "VALUES('SUV', 55.95, 50.75, 45.95, 38.95);"
        DoCmd.RunSQL "INSERT INTO Categories(Category, " & _
                     "DailyRate, WeeklyRate, MonthlyRate, WeekendRate) " & _
                     "VALUES('Truck', 52.95, 48.75, 44.95, 38.95);"
        DoCmd.RunSQL "INSERT INTO Categories(Category, " & _
                     "DailyRate, WeeklyRate, MonthlyRate, WeekendRate) " & _
                     "VALUES('Van', 65.95, 62.75, 55.95, 50.95);"
        DoCmd.RunSQL "CREATE TABLE Cars(" & _
                     "CarID AutoIncrement(100001, 1) Primary Key Not Null, " & _
                     "TagNumber VarChar(50), " & _
                     "Make VarChar(50), " & _
                     "Model VarChar(50), " & _
                     "CarYear Integer, " & _
                     "CategoryID Integer References Categories(CategoryID), " & _
                     "HasCDPlayer YesNo, " & _
                     "HasDVDPlayer YesNo, " & _
                     "Available YesNo);"
        MsgBox "A table named Cars has been created."
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('CDJ-85F', " & _
                     "'Mercury', 'GrandMarquis', " & _
                     "2008, 1004, False, True, False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('FGE-920', 'Ford', 'Escape', " & _
                     "2006, 1006, False, False, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('GMM-186', " & _
                     "'Mercury', 'Grand Marquis', " & _
                     "2007, 1004, True, True, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('GHL-22G', 'Lincoln', 'TownCar', " & _
                     "2008, 1004, True, True, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('HHS-382', 'Hyundai', 'Sonata', " & _
                     "2008, 1002, False, False, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('LBN-755', 'Lincoln', 'Navigator', " & _
                     "2008, 1006, True, True, False)"
        
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('FDX-984', 'Kia', 'Sephia', " & _
                     "2008, 1002, True, True, True);"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('AFS-888', 'Ford', 'SportTrac', " & _
                     "2008, 1007, False, True, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('CAM-422', 'Chevrolet', 'Metro', " & _
                     "2008, 1001, False,  False, False)"
    
        
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('QFH-608', 'Ford', 'F150', " & _
                     "2009, 1007, False,  True,  False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('DCC-713', 'Chevrolet', 'Camaro', " & _
                     "2009, 1003, False, False, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('LFT-268', 'Ford', 'Club Wan', " & _
                     "2008, 1005, True, True, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('PBR-69G', 'Buick', 'Regal'," & _
                     " 2008, 1004, True, True, False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('DBP-832', 'Buick', 'Park Avenue', " & _
                     "2009, 1004, False, True, False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('FM-685', 'Ford', " & _
                     "'Mustang Convertible', " & _
                     "2008, 1003, False,  False,  True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('FAX-48T', 'Mecury', 'Villager', " & _
                     "2009, 1005, False,  False,  True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('DPM-42', 'Pontiac', 'Mountana', " & _
                     "2008, 1005, True,  True,  False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('AFW-928', 'Ford', " & _
                     "'Windstar Minivan GL', 2009, " & _
                     "1005, False, False, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('UFX-963', 'Cadillac', " & _
                     "'Sedan de Ville', 2008, 1004, True, True, False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('KCR-656', 'Chevrolet', 'Blazer', " & _
                     "2009, 1006, False, True, False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('LLT-358', 'Lincoln', 'City Car', " & _
                     "2006, 1004, True, True, False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('RBL-618', 'Buick', 'LeSabre', " & _
                     "2008, 1004, True, True, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('WFV-688', 'Ford', 'E350', " & _
                     "2009, 1008, False,  False,  False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('GCV-557', 'Chevrolet', 'Camaro', " & _
                     "2009, 1003, True, False, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('TPG-905', 'Pontiac', 'Grand Am', " & _
                     "2008, 1002, False, False, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('JYW-682', 'Jeep', 'Wrangler', " & _
                     "2007, 1006, False, False, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('DFR-214', 'Ford', 'Ranger', 2008, " & _
                     "1007, False, True, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('RKR-670', 'Kia', 'Rio', 2008, " & _
                     "1001, False, False, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('YJC-498', 'Ford', 'Escort', " & _
                     "2006, 1001, False,  False,  True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('QDC-922', 'Dodge', 'Caravan', " & _
                     "2002, 1005, False, False, False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('DSS-374', 'Hyundai', 'Accent', " & _
                     "2009, 1001, False, False, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('SCC-262', 'Chrysler', 'Concorde', " & _
                     "2008, 1004, True, True, True)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('BDI-588', 'Dodge', 'Intrepid', " & _
                     "2008, 1002, False, False, False)"
    
        DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _
                     "CategoryID, HasCDPlayer, HasDVDPlayer, " & _
                     "Available) VALUES('MCM-952', 'Jaguar', 'S-Type', " & _
                     "2009, 1004, True, True, True)"
    End Sub
  12. Return to Microsoft Access
  13. Switch the form to Form View and click the button
  14. Click OK each time to create the table and create its records
  15. Close the form
  16. When asked whether you want to save it, click No

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.

When creating the child table, remember to create a column that would serve as the link with the parent table. Here are examples of the necessary tables:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Genders(" & _
                 "GenderID COUNTER(1,1) PRIMARY KEY NOT NULL," & _
                 "Gender varchar(20));"
    MsgBox "A table named Genders has been created"
    
    DoCmd.RunSQL "INSERT INTO Genders(Gender) VALUES('Male');"
    DoCmd.RunSQL "INSERT INTO Genders(Gender) VALUES('Female');"
    DoCmd.RunSQL "INSERT INTO Genders(Gender) VALUES('Unknown');"
    
    DoCmd.RunSQL "CREATE TABLE Persons(" & _
                 "PersonID COUNTER(1,1) PRIMARY KEY NOT NULL," & _
                 "FirstName varchar(20)," & _
                 "LastName varchar(20) NOT NULL," & _
                 "GenderID Integer NULL);"
    MsgBox "A table named Persons has been created"
    
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Gertrude', 'Monay', 2);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Raymond', 'Kouma', 1);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName) " & _
                 "VALUES('Peter', 'Mukoko');"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Wally', 'Bastion', 1);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Sylla', 'Nguyen', 3);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Donald', 'Wallace', 1);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Hermine', 'Khan', 2);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName) " & _
                 "VALUES('Jamie', 'Thomas');"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName) " & _
                 "VALUES('Campbell', 'Barns');"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Paula', 'Barners', 2);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Chrissie', 'Dentd', 2);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Ernestine', 'Essiane', 2);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName) " & _
                 "VALUES('Hallio', 'Randt');"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Helene', 'Cranston', 2);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Hoanga', 'Palau', 3);"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName) " & _
                 "VALUES('Phaolin', 'Krazucki');"
    DoCmd.RunSQL "INSERT INTO Persons(FirstName, LastName, GenderID) " & _
                 "VALUES('Frank', 'Cranston', 1);"
End Sub

Join Creation

Equipped with the necessary tables and their columns, you can create the join(s). To do this, on the Ribbon, you can click Create. In the Other section, click Query Design. 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:

People

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

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

Creating an Inner Join

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

Genders

Persons

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 an inner join. To do this, you would specify the TypeOfJoin factor of our formula with the expression INNER JOIN. Here is an example:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Persons.LastName, " & _
                   "Persons.FirstName, Persons.GenderID, " & _
                   "Genders.GenderID , Genders.Gender " & _
                   "FROM Persons " & _
                   "INNER JOIN Genders " & _
                   "ON Persons.GenderID = Genders.GenderID"
                
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtPersonsGenderID.ControlSource = "Persons.GenderID"
    txtGendersGenderID.ControlSource = "Genders.GenderID"
    txtGender.ControlSource = "Gender"
End Sub

This would produce:

Join

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:

Join

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 LearningPractical Learning: Using Inner Joins

  1. On the Ribbon, click Create and, in the Other section, click Query Design
  2. On the Show Table dialog box, double-click Categories and Cars
  3. Click Close
  4. In the Categories list, double-click Category
  5. In the Cars list, double-click Make, Model, CarYear, and Available
  6. Save the query as CarsInventory
     
    Cars Inventory
  7. Right-click somewhere in the window and click SQL View
     
    Cars Inventory
  8. Notice the INNER JOIN expression in the statement.
    Close the query
  9. To create a new form, on the Ribbon, click Create
  10. In the Forms section, click More Forms -> Form Wizard
  11. In the Tables/Queries combo box, select Query: CarsInventory
  12. Click the Select All button
  13. Click Next
  14. When asked how you want to view your data, Accept By Categories and click Next
  15. Click Tabular and click Next
  16. Click Apex and click Next
  17. Accept the options and click Finish
  18. Adjust design as you see fit
     
  19. Close the form
  20. When asked whether you want to save it, click Yes
 
 

 

 
 

Outer Joins

 

Introduction

Instead of showing only records that have entries in the child table, you may want your statement 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:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Persons.PersonID, " & _
                   "       Persons.FirstName, " & _
                   "       Persons.LastName, " & _
                   "       Genders.GenderID, " & _
                   "       Genders.Gender " & _
                   "FROM Persons " & _
                   "LEFT OUTER JOIN Genders " & _
                   "ON Persons.GenderID = Genders.GenderID"
                
    txtPersonID.ControlSource = "PersonID"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtGendersGenderID.ControlSource = "GenderID"
    txtGender.ControlSource = "Gender"
End Sub

This would produce:

Join

Join

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:

Join

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

Join Properties

This would change the join into a left join.

Practical Learning Practical Learning: Using Left Outer Joins

  1. On the Ribbon, click Create and, in the Other section, click Query Design
  2. On the Show Table dialog box, double-click Categories and Cars
  3. Click Close
  4. In the Categories list, double-click Category
  5. In the Cars list, double-click Make, Model, CarYear, and Available
  6. Right-click somewhere in the window and click SQL View
  7. Right-click the title bar of the window and click SQL VIEW
  8. Change the INNER JOIN expression to LEFT OUTER JOIN
     
  9. Right-click the window's tab and click Design View
     
    Join
  10. To see the result, right-click the Query1 tab and click Datasheet View
  11. Notice that the result is the same
  12. Right-click the tab of the window and click Design View
  13. To add a few columns, in the Cars list, drag HasCDPlayer and drop it on top of Available in the lower section
  14. In the Categories list, click DailyRate, press and hold Ctrl
  15. Click WeekendRate and release Ctrl
  16. Drag the selected columns and drop them on top of Available in the lower section
     
    Join
  17. Save the query as CarsOptions
  18. Close the query
  19. To create a new form, on the Ribbon, click Create
  20. In the Forms section, click More Forms -> Form Wizard
  21. In the Tables/Queries combo box, select Query: CarsOptions
  22. Click the Select All button
  23. Click Next
  24. In the second page of the wizard, click Tabular
  25. Click Next
  26. Click Aspect and click Next
  27. Accept the suggested name of the form and click Finish
  28. Adjust design as you see fit
     
    Cars
     
  29. Close the form
  30. When asked whether you want to save it, click Yes

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 value for the GenderID column. After the first record, the right outer join 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:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Persons.PersonID, " & _
                   "       Persons.FirstName, " & _
                   "       Persons.LastName, " & _
                   "       Genders.GenderID, " & _
                   "       Genders.Gender " & _
                   "FROM Persons " & _
                   "RIGHT OUTER JOIN Genders " & _
                   "ON Persons.GenderID = Genders.GenderID"

    txtPersonID.ControlSource = "PersonID"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtGendersGenderID.ControlSource = "GenderID"
    txtGender.ControlSource = "Gender"
End Sub

This would produce:

Join

Join

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

Join Properties

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. To specify a criterion, in the Criteria box corresponding to the column, type the condition. Here is an example:

Join

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

Join

And you can use it as a record source for a report or a form. Here is an example:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Persons.FirstName, " & _
                   "       Persons.LastName, " & _
                   "       Genders.Gender " & _
                   "FROM Persons " & _
                   "INNER JOIN Genders " & _
                   "ON Persons.GenderID = Genders.GenderID " & _
                   "WHERE Genders.Gender = 'Female';"

    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtGender.ControlSource = "Gender"
End Sub

Join

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

 
 
   
 

Previous Copyright © 2005-2011 FunctionX, Inc. Next