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:
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.
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
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:
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
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.
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 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:
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: Introducing Relationships
