Query Criteria

[w]

Hello, I am having trouble with one of my queries. Here is
the SQL and below it is an explanantion of what I am
trying to do:

SELECT Courses.courseID, Courses.level, Count(Courses.cId)
AS CountOfcId, Sum(Courses.studcount) AS SumOfstudcount,
Count(Courses.InstId) AS CountOfInsId
FROM Courses
GROUP BY Courses.courseID, Courses.level;

Here is the basic explanation: courses is the table that
contains the information. CourseID is the title of the
course like English or math etc. Level is like 101 102
etc. cId is the primary key for each record input into
the system. InsId is the identifier for each instructor
and studcount is the number of students in the each class.
Basically I want to see how many courses were taught, how
many students in each class, and how many unique
instructors there were for each course. The result of my
query looks something like this:

CourseID Level CountOfcID SumOfstudcount CountOfInsId
English 101 3 40 3
English 102 5 25 5

As you can see for the count of InsId in English 101, I
get a value of 3 and I am also getting the
same number of instructors as there were courses taught(ie
countOfcID=3) when in reality there was one instructor for
all three courses. How do I get it to count the unique
number of instructors within a set? Is there a better way
to do this? Any help is appreciated!

Thanks!

 

[John Vinson]

As you can see for the count of InsId in English 101, I
get a value of 3 and I am also getting the
same number of instructors as there were courses taught(ie
countOfcID=3) when in reality there was one instructor for
all three courses. How do I get it to count the unique
number of instructors within a set?

Access does not support the useful COUNT DISTINCT operation, even
though it's ANSI standard SQL. The Count() operation doesn't count
instructor ID's, it counts the number of records that meet the
criteria.

I suspect you'll need a Subquery:

SELECT Courses.courseID, Courses.level, Count(Courses.cId)
AS CountOfcId, Sum(Courses.studcount) AS SumOfstudcount,
(SELECT Count(*) FROM Instructors WHERE Instructors.InstID IN
(SELECT InstID FROM Courses AS X WHERE X.CourseID = Courses.CourseID))
AS CountOfInsId
FROM Courses
GROUP BY Courses.courseID, Courses.level;

 

[w]

Thank you for your response. I think what you gave me is
getting me where I want except there is one minor
problem. The query is counting total instructors for each
course without taking into account the level of the
course. So if there were 5 instructors for English the
total count was 5 for both English 101 and English 102. I
think that the query should include an additional
statement to the following part:

"(SELECT InstID FROM Courses AS X WHERE X.CourseID =
Courses.CourseID))"

I tried adding this to it :

AND AS Y WHERE Y.level=Courses.level

but I got an error message. How can I program it to add
two criteria for the count instead of only one?

Thanx!

Thanks again!!

 

[John Vinson]

Thank you for your response. I think what you gave me is
getting me where I want except there is one minor
problem. The query is counting total instructors for each
course without taking into account the level of the
course. So if there were 5 instructors for English the
total count was 5 for both English 101 and English 102. I
think that the query should include an additional
statement to the following part:

"(SELECT InstID FROM Courses AS X WHERE X.CourseID =
Courses.CourseID))"

I tried adding this to it :

AND AS Y WHERE Y.level=Courses.level

but I got an error message. How can I program it to add
two criteria for the count instead of only one?

The "AS X" Defines an alias for the second instance of Courses - it is
part of the FROM clause, not the WHERE clause; and you don't need a
third instance. Just use

(SELECT InstID FROM Courses AS X WHERE X.CourseID =
Courses.CourseID AND X.Level=Courses.level))