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: |
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: |
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 |
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 |
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 |
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-2016, FunctionX | Next |
|