One of the most confusing things about relationships and queries in Microsoft’s Access is using Join Properties to link properties between two tables. Finding an effective technique to help users understand this concept can be difficult. This article shares a technique that I’ve developed that seems to work well in the classroom.
I don't get the results I want
I often hear users complain about not getting the results they want or expect when querying a database. This may be due to the confusion surrounding the choices in the Join Properties dialog box, so it’s important not to ignore this lesson—even in a beginning level class on queries.
After teaching the basics of queries, including the Null concept, I review the structure of our training database to set up a new problem. Our training database contains a list of classes that is related to the Trainers table shown in Figure A.
|Relationship between classes and trainer tables|
I open both the Classes and Trainers tables and note how many records each table contains on the whiteboard at the front of the class.
Then I ask students to work with me to create a query that will show us all the classes that do not have trainers assigned to them. In order to show how Join Properties works, I ask them to use both the Classes and the Trainers tables. While it’s not necessary in this case, I explain that using both will help us understand the ways that two tables can talk with one another. Typically, after a bit of discussion, the class comes up with a query similar to what is shown in Figure B.
|First attempt at a query for classes with no trainers|
When students view the results of the query, they find no matching records. While some students think this may be correct, someone usually remembers that when we looked at the tables earlier, we saw a class without a trainer assigned. If no one catches this error, I point it out and suggest that we must have made a mistake.
Introducing Join Properties
At this point, I tell the students that there is a twist they need to know about when dealing with queries—a twist that can create unexpected results.
After going into Design View, I explain that there are options in deciding how the two tables will talk to one another. Then I open the Join Properties dialog box, as shown in Figure C.
I explain that the choices here can change the results of a query. I ask them to note that the default setting is the first option: Only Include Rows Where The Joined Fields From Both Tables Are Equal. After clicking OK without making any changes, we remove the Is Null condition, add a few extra fields from the Trainers table, and run the query again. I then ask the trainees several questions:
- How many total records were found?
- How many of the records contained class information?
- How many of the records contained trainer information?
I note the answers on the whiteboard and do not offer any further explanation at this point.
Running the query again
The second time the query is run, I simply choose the second option in the Join Properties dialog box: Include ALL Records From ‘tblTrainers’ And Only Those Records From ‘tblClasses’ Where The Joined Fields Are Equal. This time, with all records from tblTrainers included, the results are a bit different, as shown in Figure D.
|Query results showing "ALL" trainers|
Using the whiteboard to note results, I again ask the students the number of total records found, the number of records with class information, and the number of records with trainer information. We then review the original question: “What classes do not have any trainers assigned?” We decide that we cannot tell if we have answered the question at this point and move on. I continue not to explain Join Properties, preferring for the examples to speak for themselves.
Third time's the charm
Once again, I open the Join Properties dialog box, this time selecting the third option: Include ALL Records From ‘tblClasses’ And Only Those Records From ‘tblTrainers’ Where The Joined Fields Are Equal. The query is run again, and the results are shown in Figure E.
|Query results showing "ALL" classes|
We use the whiteboard, shown in Figure F, to note the third set of results: the number of total records found, the number of records with class information, and the number of records with trainer information. After reviewing the original question, “What classes do not have any trainers assigned?” it becomes clear that the third Join Properties option will give us the desired answers.
We edit the query design one more time and add the Is Null condition for TrainerID to the final query. In the end, we find that we do have a couple of classes without trainers assigned.
|Sample whiteboard notes after all the queries|
Bringing it all together
After going through each of the three examples, it becomes much easier to have a formal discussion about Join Properties. In about five minutes, I recap what each of the different properties did to our results and why. I emphasize that users may not get it right the first time; the important thing to remember is that Join Properties exists and that they can try a query each way until they get the desired results. Although this lesson is done in the context of a query class, I also point out that Join Types can be set in the Relationships window; however, those choices can be changed for an individual query without impacting the Join Type originally set up in Relationships.
Do you have an Access training tip to share?
Access can contain a lot of tricky concepts. If you have a tip or technique for teaching one of these difficult concepts, share your ideas by posting a comment to this article or writing to Janice Ward.