SQL JOINS in Access

I have a small SQL query. This is the SQL at the moment:

"SELECT tblClass.ClassID, tblClass.Subject, tblClass.Teacher,
sum(iif (datepart('ww',tblHomework.DateSet)=(datepart('ww',Date())) ,1,0)) as wk1,
sum(iif (datepart('ww',tblHomework.DateSet)=(datepart('ww',Date())-1) ,1,0)) as wk2,
sum(iif (datepart('ww',tblHomework.DateSet)=(datepart('ww',Date())-2) ,1,0)) as wk3,
sum(iif (datepart('ww',tblHomework.DateSet)=(datepart('ww',Date())-3) ,1,0)) as wk4
FROM tblClass LEFT JOIN tblHomework ON tblClass.ClassID = tblHomework.ClassID
WHERE (tblHomework.TaskType = 'HW' AND Subject='" & Request("Faculty") & "')
GROUP BY tblClass.ClassID, tblClass.Subject, tblClass.Teacher"

It works just fine except for one thing. tblClass contains all the classes in the school. I want all the classes to show even if they are not found in tblHomework. Basically the SQL should count the number of homeworks set for each class. If the class does not appear in tblHomework that would imply no homework has been set.

I have fiddled about with RIGHT JOIN and LEFT join but the output is the same. It only shows the classes that can be matched in both tables. I am using Access so FULL OUTER JOIN is not acceptable.

Any ideas how to solve this one?


Who is Participating?
Daniel WilsonConnect With a Mentor Commented:
Your WHERE clause is limiting you to rows that match.

Try this:

WHERE ((tblHomework.TaskType is NULL or tblHomework.TaskType = 'HW') AND (Subject IS NULL or Subject='" & Request("Faculty") & "'))

Open in new window


I believe the problem is in your WHERE clause. Even thought you are LEFT joining the tables, your WHERE clause is requesting only records that contain tblHomework.TaskType = 'HW'. This will limit your results from the tblClass table.
DesiMckAuthor Commented:
Thank you - that was very useful and well explained.  Cheers,  Desi
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.