Make this query:
Code:
SELECT TblStudents.StudentID_PK, TblProcedureList.ProcedureID_PK
FROM TblStudents, TblProcedureList;
Name it qryStudentProcedureCartesian
Create this query with the result:
Code:
SELECT qryStudentProcedureCartesian.StudentID_PK, TblStudents.StudentName, qryStudentProcedureCartesian.ProcedureID_PK, TblProcedureList.txtProcedure, Count(TblProceduresStudent.StudentProcID) AS CountOfStudentProcID
FROM ((qryStudentProcedureCartesian LEFT JOIN TblProceduresStudent ON (qryStudentProcedureCartesian.StudentID_PK = TblProceduresStudent.StudentID_FK) AND (qryStudentProcedureCartesian.ProcedureID_PK = TblProceduresStudent.ProcedureID_FK)) LEFT JOIN TblProcedureList ON qryStudentProcedureCartesian.ProcedureID_PK = TblProcedureList.ProcedureID_PK) LEFT JOIN TblStudents ON qryStudentProcedureCartesian.StudentID_PK = TblStudents.StudentID_PK
GROUP BY qryStudentProcedureCartesian.StudentID_PK, TblStudents.StudentName, qryStudentProcedureCartesian.ProcedureID_PK, TblProcedureList.txtProcedure;
this will give you a count of all the zero records as well.
or if you are comfortable with nested SQL statements
Code:
SELECT CPS.spk, TblStudents.StudentName, CPS.ppk, TblProcedureList.txtProcedure, Count(PS.StudentProcID) AS ProcCount
FROM (((SELECT TblStudents.StudentID_PK AS SPK, TblProcedureList.ProcedureID_PK AS PPK
FROM TblStudents, TblProcedureList) AS CPS LEFT JOIN TblProceduresStudent AS PS ON (CPS.PPK = PS.StudentID_FK) AND (CPS.SPK = PS.ProcedureID_FK)) LEFT JOIN TblStudents ON CPS.SPK = TblStudents.StudentID_PK) LEFT JOIN TblProcedureList ON CPS.PPK = TblProcedureList.ProcedureID_PK
GROUP BY CPS.spk, TblStudents.StudentName, CPS.ppk, TblProcedureList.txtProcedure;