PeterBaileyUk
asked on
Access query
I have this query it shows the module units each student has taken, I am not sure how to get the highest attainment ([module unit]) for each student
SELECT TblModules.Module, TblModules.[Module Unit], TblModules.[Module Unit Name], TblStudents.Student_ID
FROM TblModules RIGHT JOIN (TblCurrentPrograms RIGHT JOIN (TblStudents LEFT JOIN TblStudent_Session_Link ON TblStudents.Student_ID = TblStudent_Session_Link.Student_FK) ON TblCurrentPrograms.Programmes_ID = TblStudent_Session_Link.Programme_FK) ON TblModules.ModuleNamePK = TblCurrentPrograms.Module_FK
GROUP BY TblModules.Module, TblModules.[Module Unit], TblModules.[Module Unit Name], TblStudents.Student_ID
HAVING (((TblModules.Module) Like "Raz*"))
ORDER BY TblModules.Module;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Peter,
Did that do it for you, because now that I look at it a bit more closely, I'm certain that is not going to give you the results you want. I think you are more likely to get what you need from:
SELECT TblStudents.Student_ID, MAX(tblModules.[Module_Uni
FROM TblModules
RIGHT JOIN (TblCurrentPrograms
RIGHT JOIN (TblStudents
LEFT JOIN TblStudent_Session_Link
ON TblStudents.Student_ID = TblStudent_Session_Link.St
ON TblCurrentPrograms.Program
ON TblModules.ModuleNamePK = TblCurrentPrograms.Module_
GROUP BY tblStudents.Student_ID
HAVING (((TblModules.Module) Like "Raz*"))
ORDER BY tblStuednts.Student_ID
Then, if you need the module number and module name for that MaxStudentModUnit, you would join that query back to tblModules on the MaxStudentModUnit = tblModules.[Module_Unit]
Did that do it for you, because now that I look at it a bit more closely, I'm certain that is not going to give you the results you want. I think you are more likely to get what you need from:
SELECT TblStudents.Student_ID, MAX(tblModules.[Module_Uni
FROM TblModules
RIGHT JOIN (TblCurrentPrograms
RIGHT JOIN (TblStudents
LEFT JOIN TblStudent_Session_Link
ON TblStudents.Student_ID = TblStudent_Session_Link.St
ON TblCurrentPrograms.Program
ON TblModules.ModuleNamePK = TblCurrentPrograms.Module_
GROUP BY tblStudents.Student_ID
HAVING (((TblModules.Module) Like "Raz*"))
ORDER BY tblStuednts.Student_ID
Then, if you need the module number and module name for that MaxStudentModUnit, you would join that query back to tblModules on the MaxStudentModUnit = tblModules.[Module_Unit]
ASKER