Query with inner join question

[beckyr51]

I have a query that returns if someone has free space (determined by taking the number of students they have from their chamber size) this works fine if those people have students, but if they dont have students they dont show. How do i incorporate that if they have no students their chamber size is shown? Here's my sql syntax:

SELECT tblTutor.TU_CODE, tblTutor.TU_TITLE, tblTutor.TU_FORENAME, tblTutor.TU_SURNAME, [TU_CHAMBER_SIZE]-Count([STU_TU_CODE]) AS Free
FROM (tblFaculty INNER JOIN tblTutor ON tblFaculty.FAC_NO=tblTutor.TU_FAC_NO) INNER JOIN tblStudents ON tblTutor.TU_CODE=tblStudents.STU_TU_CODE
GROUP BY tblTutor.TU_CODE, tblTutor.TU_TITLE, tblTutor.TU_FORENAME, tblTutor.TU_SURNAME, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_FAC_NO, tblFaculty.FAC_NO, tblFaculty.FAC_NAME, tblStudents.STU_TU_CODE
HAVING ((([TU_CHAMBER_SIZE]-Count([STU_TU_CODE]))>0) AND ((tblFaculty.FAC_NAME) Like "*" & [Please enter the Faculty name] & "*"));

 

[djj55]

Could

([TU_CHAMBER_SIZE]-Count([STU_TU_CODE]))>0)

be causing the problem?
djj

 

[PHV]

Replace this:
INNER JOIN tblStudents
with this:
LEFT JOIN tblStudents

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[beckyr51]

Thank you PHV that worked