Simple SQL causing hair loss! :)

[andyw2s]

Hi,

Hoping someone can help with what should be a simple problem.

Have two tables:

Classes and Enroll

Classes contains some classes with a ClassID, Enroll has a ClassID and a MemberID.

When a member enrolls in a class a entry is made in the enroll table with their member ID and the class ID.

I want to be able to retrieve a list of classes with the number of people enrolled to the class, am using the following:

SELECT Class.Name, COUNT (*) AS EnrollCount
FROM Class left JOIN Enroll ON Enroll.ClassID = Class.ClassID
GROUP BY Class.Name

This is working except that classes with no members enrolled are still showing as one (I assume because the COUNT is counting the entry from the left join). If I use a inner join it simply does not show the classes with no members joined. I need a display of classes with number of members including those with no members.

Please help!

[ADezii]

The following will give the correct results, but I'm sure that the SQL Gang will have a more efficient solution than mine (just drawing a blank on this one):

SELECT Class.Name, fClassCount([Class].[ClassID]) AS [Class Count]
FROM Class LEFT JOIN Enroll ON Class.ClassID = Enroll.ClassID
GROUP BY Class.Name, Class.ClassID;

Public Function fClassCount(lngClassID As Long)
  fClassCount = DCount("*", "Enroll", "[ClassID] = " & lngClassID)
End Function

[Stewart Ross]

Hi, and Welcome to Bytes!

As Adezii thought, there is a simple solution in SQL. The problem is the use of Count(*) which as you mention is simply counting the rows - so it returns a count of minimum 1 for each class name listed. This can be resolved simply by using Count(Class.ClassID) instead.

The SQL then just becomes

SELECT Class.Name, COUNT (Class.ClassID) AS EnrollCount
FROM Class left JOIN Enroll ON Enroll.ClassID = Class.ClassID
GROUP BY Class.Name

It works because the Count function in Access does not count nulls when given a specific field name as a qualifier, and the nulls in this case are where there are no Enroll table rows for that class as yet

-Stewart

[ADezii]

You didn't have to make it look that simple, Stewart! (LOL)