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 at least two 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
 

Previous Copyright © 2005-2010 FunctionX, Inc. Next