CODEWITHSUNDEEP
sqlms-accessms-access-2013
user2293224
user2293224

Reputation: 2220

left Join query in access 2013

I am new to Microsoft Access. Accept my apology if my question seems trivial.

I am trying to write a query in Access that shows the total number of students enrolled in courses on a monthly basis. I have two tables named course and confirmed_enrollments.

The course table has only one field named course_name whereas the confirmed_enrolments table has three fields; student_code, course_name, and month_of_enrol.

I want to show all course_name (whether students enrolled in it or not) in my query and their total enrollments of the particular month. The query I have written only shows the courses that have enrollment and does not consider the courses which does not have enrollment.

Looking for your help. Here is my SQL code:

SELECT Course.Course_name, 
        Count(confirmed_enrolments.Student_code) AS CountOfStudent_code,
        confirmed_enrolments.Month_of_enrol
    FROM Course 
         LEFT JOIN confirmed_enrolments 
             ON Course.Course_name = confirmed_enrolments.Course_name
    GROUP BY Course.Course_name, confirmed_enrolments.Month_of_enrol
    HAVING confirmed_enrolments.Month_of_enrol="December 2016";

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you intend this:

SELECT c.Course_name, Count(ce.Student_code) AS CountOfStudent_code, 
       ce.Month_of_enrol
FROM Course as c LEFT JOIN
     (SELECT ce.*
      FROM confirmed_enrolments as ce
      WHERE ce.Month_of_enrol = "December 2016"
     ) as ce
     ON c.Course_name = ce.Course_name
GROUP BY c.Course_name, ce.Month_of_enrol;

The use of the HAVING clause in your query is clever, but you are filtering by the second table -- and the value is NULL rather than the specified month -- when there are no matches.

The issue is that the condition is on the second table. In a normal SQL dialect, you would write:

SELECT c.Course_name, Count(ce.Student_code) AS CountOfStudent_code, 
       ce.Month_of_enrol
FROM Course as c LEFT JOIN
     confirmed_enrolments as ce
     ON c.Course_name = ce.Course_name AND
        ce.Month_of_enrol = "December 2016"
GROUP BY c.Course_name, ce.Month_of_enrol;

But MS Access doesn't seem to allow the second comparison in a LEFT JOIN.

By the way, another way to write the query uses a correlated subquery:

SELECT c.Course_name,
       (SELECDT Count(ce.*) 
        FROM confirmed_enrolments as ce
        WHERE ce.Month_of_enrol = "December 2016"
       ) AS CountOfStudent_code, 
       "December 2016" as ce.Month_of_enrol
FROM Course as c ;

Upvotes: 1

Related Questions