Well, I understand that a left join should show all records from table A and only those records in table B that match.
Well, it doesnt work in my case. This is query before (A):
SELECT *
FROM ((tblStudents LEFT JOIN tblStudentsBus ON tblStudents.strStudentID = tblStudentsBus.strStudentID) LEFT JOIN tblStudentsMedical ON tblStudents.strStudentID = tblStudentsMedical.strStudentID) LEFT JOIN tblUserStudentsSelection ON tblStudents.strStudentID = tblUserStudentsSelection.strStudentID
WHERE (((tblStudents.dtmEntryDate)<=[Forms]![frmToDo]![gdtmVirtualDate]) AND ((tblStudents.dtmLeavingDate)>[Forms]![frmToDo]![gdtmVirtualDate] Or (tblStudents.dtmLeavingDate) Is Null) AND ((tblUserStudentsSelection.strUserID)=[application].[currentuser]));
and this after (B):
SELECT *
FROM (((tblStudents LEFT JOIN tblStudentsBus ON tblStudents.strStudentID = tblStudentsBus.strStudentID) LEFT JOIN tblStudentsMedical ON tblStudents.strStudentID = tblStudentsMedical.strStudentID) LEFT JOIN tblUserStudentsSelection ON tblStudents.strStudentID = tblUserStudentsSelection.strStudentID) LEFT JOIN tblStudentsClasses ON tblStudents.strStudentID = tblStudentsClasses.strStudentID
WHERE (((tblStudents.dtmEntryDate)<=[Forms]![frmToDo]![gdtmVirtualDate]) AND ((tblStudents.dtmLeavingDate)>[Forms]![frmToDo]![gdtmVirtualDate] Or (tblStudents.dtmLeavingDate) Is Null) AND ((tblUserStudentsSelection.strUserID)=[application].[currentuser]) AND ((tblStudentsClasses.strClassID) Like "*" & [Forms]![frmToDo]![txtCurrentSchoolyear] & "*"
);
Some explanation: Query A is supposed to pick all parents that have students in the school at a current date ([Forms]![frmToDo]![gdtmVirtualDate]). The rest are mostly 1:1 tables with additional data. This wirks fine in Query A.
In Query B I tried to attach [tblStudentsClasses], a list of all classes the student attends. Query B is supposed to show the same as Query A + the Current Class (comparing [Forms]![frmToDo]![txtCurrentSchoolyear] - the schoolyear is hidden in the [strClassID])... so it should have the same number of records as Query A ...
... although this is a left Join, Query B SKIPPS all Students that do not have a current Schoolyear-Class assigned. I read in some threads Access is supposed to fill those records with NULL Fields (when they don't exist). Here this is not happening - the record is simply skipped. Anybody has a clue why this is happening?
Well, it doesnt work in my case. This is query before (A):
SELECT *
FROM ((tblStudents LEFT JOIN tblStudentsBus ON tblStudents.strStudentID = tblStudentsBus.strStudentID) LEFT JOIN tblStudentsMedical ON tblStudents.strStudentID = tblStudentsMedical.strStudentID) LEFT JOIN tblUserStudentsSelection ON tblStudents.strStudentID = tblUserStudentsSelection.strStudentID
WHERE (((tblStudents.dtmEntryDate)<=[Forms]![frmToDo]![gdtmVirtualDate]) AND ((tblStudents.dtmLeavingDate)>[Forms]![frmToDo]![gdtmVirtualDate] Or (tblStudents.dtmLeavingDate) Is Null) AND ((tblUserStudentsSelection.strUserID)=[application].[currentuser]));
and this after (B):
SELECT *
FROM (((tblStudents LEFT JOIN tblStudentsBus ON tblStudents.strStudentID = tblStudentsBus.strStudentID) LEFT JOIN tblStudentsMedical ON tblStudents.strStudentID = tblStudentsMedical.strStudentID) LEFT JOIN tblUserStudentsSelection ON tblStudents.strStudentID = tblUserStudentsSelection.strStudentID) LEFT JOIN tblStudentsClasses ON tblStudents.strStudentID = tblStudentsClasses.strStudentID
WHERE (((tblStudents.dtmEntryDate)<=[Forms]![frmToDo]![gdtmVirtualDate]) AND ((tblStudents.dtmLeavingDate)>[Forms]![frmToDo]![gdtmVirtualDate] Or (tblStudents.dtmLeavingDate) Is Null) AND ((tblUserStudentsSelection.strUserID)=[application].[currentuser]) AND ((tblStudentsClasses.strClassID) Like "*" & [Forms]![frmToDo]![txtCurrentSchoolyear] & "*"
);Some explanation: Query A is supposed to pick all parents that have students in the school at a current date ([Forms]![frmToDo]![gdtmVirtualDate]). The rest are mostly 1:1 tables with additional data. This wirks fine in Query A.
In Query B I tried to attach [tblStudentsClasses], a list of all classes the student attends. Query B is supposed to show the same as Query A + the Current Class (comparing [Forms]![frmToDo]![txtCurrentSchoolyear] - the schoolyear is hidden in the [strClassID])... so it should have the same number of records as Query A ...
... although this is a left Join, Query B SKIPPS all Students that do not have a current Schoolyear-Class assigned. I read in some threads Access is supposed to fill those records with NULL Fields (when they don't exist). Here this is not happening - the record is simply skipped. Anybody has a clue why this is happening?
