SOLVED

Missing data when joining tables in a query?

Occasional Contributor

I'm brand new to Access, so not sure if I'm even asking the right question.

 

I have one Table, tblStudents, that has many rows of students with columns such as student ID, student name, student mentor, etc. Another table, tblMentors, is joined to tblStudents that table via the Mentor ID column.

 

When I drag tblStudents into the query design window and make a quick query with just their names and IDs, it returns 1930 students. However, as soon as I drag tblMentors into the design window, without changing anything else about the query, only 1116 students show up. This is remains true when I "reverse the join" by draging tblMentors into the design window before tblStudents. I assume this is happening because many students do not have a mentor, and thus the cell in their mentor ID column is blank--I think all of these students are just being excluded from the query. But I want to include all the students.

 

Is there any way for me to fix this by adding something like "includeNullValues = TRUE" to the SQL? Or is it possible to fix it by applying Nz() to all of the tables referenced by the query? How?

 

SQL that returns 1930 students:

SELECT tblStudents.StudentID, tblStudents.LastName, tblStudents.FirstName, tblStudents.Class

FROM tblStudents;

 

SQL that returns 1116 students:

SELECT tblStudents.StudentID, tblStudents.LastName, tblStudents.FirstName, tblStudents.Class

FROM tblMentors INNER JOIN tblStudents ON tblMentors.MentorID = tblStudents.MentorID;

 

SQL that returns 1116 students (with reversed join):

SELECT tblStudents.StudentID, tblStudents.LastName, tblStudents.FirstName, tblStudents.Class

FROM tblMentors INNER JOIN tblStudents ON tblMentors.MentorID = tblStudents.MentorID;

6 Replies
best response confirmed by sarahjane2046 (Occasional Contributor)
Solution

 

First a bit of fiddly terminology accuracy.


"Cells" are in Excel and Word. In Access, you have "Fields" in tables, not cells.

 

You have an INNER JOIN, which as you realize only returns records which match on MentorID.
If you want to return ALL records from one table, and only MATCHING records from the other table, you need to use an OUTER JOIN.

Learn more about Join Types here. 

Thank you so much!
I've changed my SQL to
FROM tblStudents LEFT JOIN tblStudents ON tblStudents.MentorID=tblMentors.MentorID;

However, it says that there is a syntax error in the JOIN statement? I feel like I'm missing something obvious
FROM tblStudents LEFT OUTER JOIN tblStudents ON tblStudents.MentorID=tblMentors.MentorID;

Also causes a syntax error
Nevermind, I'm dumb

FROM tblStudents LEFT OUTER JOIN tblMentors ON tblStudents.MentorID=tblMentors.MentorID;
I sort of sand-bagged you a little by NOT providing the full syntax, partly because I think learning by trying is more complete than learning by copy-paste. And along the same lines, no one is dumb when they try and don't succeed but keep on trying until they do succeed. That's how we all are on our good days.

Continued success with the project.