VBA Programming in Microsoft Access: Data Joins

	Data Joins

Introduction

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:

Choosing the tables that will be involved in the join
Selecting the column that will create the link in each table
Formulating a SQL statement that will produce the records

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 the parent table 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 cmdCreateTables_Click()
    DoCmd.RunSQL "CREATE TABLE Departments(" & _
                 "DeptCode char(5) PRIMARY KEY NOT NULL," & _
                 "Department varchar(50));"
    MsgBox "A table named Departments has been created."
    
    DoCmd.RunSQL "INSERT INTO Departments VALUES('HMNRS', 'Human Resources');"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('RESDV', 'Research & Development');"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('ITMNG', 'Information Technology & Management');"
    
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "EmplNbr char(7) PRIMARY KEY NOT NULL," & _
                 "FirstName varchar(20)," & _
                 "LastName varchar(20) NOT NULL," & _
                 "DeptCode char(5) NULL);"
    MsgBox "A table named Employees has been created."
    
    DoCmd.RunSQL "INSERT INTO Employees VALUES('204-517', 'Albertine', 'Walley', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('792-826', 'Rick', 'Bowden', 'HMNRS');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('274-759', 'Lisa', 'Brayer');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('729-475', 'Wally', 'Bastion', 'HMNRS');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('759-794', 'Sylla', 'Nguyen', 'ITMNG');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('824-004', 'Donald', 'Wallace', 'HMNRS');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('279-405', 'Hermine', 'Khan', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('248-295', 'Jamie', 'Thomas');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('979-514', 'Campbell', 'Barns');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('297-924', 'Paula', 'Barners', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('249-920', 'Chrissie', 'Dentd', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('204-058', 'Ernestine', 'Essiane', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('828-294', 'Hallio', 'Randt');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('279-750', 'Helene', 'Cranston', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('294-759', 'Hoanga', 'Klein', 'ITMNG');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('974-003', 'Phaolin', 'Krazucki');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('208-095', 'Frank', 'Burditt', 'HMNRS');"
End Sub

Inner Joins

Imagine you have two tables that can be linked through one's primary key and another's foreign key. When creating a query of records of the Employees 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 use the INNER JOIN expression. Here is an example:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.LastName, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.DeptCode, " & _
                   "       Departments.DeptCode, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode"
                
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtEmployeeDepartment.ControlSource = "Employees.DeptCode"
    txtDepartmentCode.ControlSource = "Departments.DeptCode"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

Join

Left Outer Joins

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.

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 of our formula with LEFT JOIN or LEFT OUTER JOIN. Here is an example:

Private Sub cmdDataSource_Click()
    Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.DeptCode, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "LEFT OUTER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartmentCode.ControlSource = "Departments.DeptCode"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

Join

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()
    Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.DeptCode, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "RIGHT OUTER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartmentCode.ControlSource = "Departments.DeptCode"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

Join

Notice that the query result starts with the first record of the parent table, also called the left table, and lists the records of the child table, also called the right table, that have the entry corresponding to that first record. Then it moves to the next value.

Data Filtering and Joins

Just as done for a SQL statement that involves only one table, you can filter records using any field(s) for one or more of the tables in the statement. To do this, after the join statement, add your WHERE clause with the appropriate expression. Here is an example:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE Employees.LastName = 'Klein';"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

Filtering Record in a Join

As mentioned already, you can use any available column(s) of the tables. Here is an example that uses a field of the parent table:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE Departments.Department = 'Human Resources';"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

Filtering Record in a Join