SQL Query - Count() and Inner Join

Question

I want to use an inner join to list the student ID, name, and total number of timetabled hours the student has per week, when the student has more than 4 hours per week.

I have three tables required here, student, studentReg and roomBooking as follows

 student

 id | fname | surname | courseCode

studentReg

sID | modCode

roomBooking

bookingID | roomCode | moduleCode | dayReq | timeReq | semester | classSize

The SQL query I have so far is

 SELECT COUNT(moduleCode) AS [Lecture Hours],
 id, fname, surname
 FROM (student INNER JOIN studentReg ON student.id = studentReg.sID
        INNER JOIN roomBooking ON studentReg.modCode = roomBooking.moduleCode)
 HAVING COUNT (moduleCode) > 4;

and when I try to run this, I get "syntax error in expression"

Can anyone help me as to what the problem is?

Answer 1

Never sure with nested join in ms access, but I would try something like that

SELECT COUNT(moduleCode) AS [Lecture Hours],
 id, fname, surname
 FROM student 
 INNER JOIN (studentReg 
        INNER JOIN roomBooking ON studentReg.modCode = roomBooking.moduleCode)
        ON student.id = studentReg.sID
 GROUP BY id, fname, surname
 HAVING COUNT (moduleCode) > 4

or maybe

SELECT COUNT(moduleCode) AS [Lecture Hours],
 id, fname, surname
 FROM (student INNER JOIN studentReg ON student.id = studentReg.sID)
        INNER JOIN roomBooking ON studentReg.modCode = roomBooking.moduleCode
 GROUP BY id, fname, surname
 HAVING COUNT (moduleCode) > 4;