query criteria

[w]

Hi! I am having trouble with one of my queries. Basically
I have a database where one can enter in course type,
instructors, students, number of students, etc. What I
have done is created a query that groups on the course
type and then counts the number of instructors within each
course type. I am able to count the number of courses that
were taught. All of what I have done seems to work fine
except that when the number of instructors is counted the
query counts the instructor for every course. So for
example, there may be 2 instructors that taught 5 English
classes and my query shows 5 instructors instead of 2. Are
there any tips on how to fix this?

Thanks!!

 

[John Vinson]

Hi! I am having trouble with one of my queries. Basically
I have a database where one can enter in course type,
instructors, students, number of students, etc. What I
have done is created a query that groups on the course
type and then counts the number of instructors within each
course type. I am able to count the number of courses that
were taught. All of what I have done seems to work fine
except that when the number of instructors is counted the
query counts the instructor for every course. So for
example, there may be 2 instructors that taught 5 English
classes and my query shows 5 instructors instead of 2. Are
there any tips on how to fix this?

Thanks!!

Please open the Query in SQL view and post the SQL here. It's all but
impossible to tell what you might be doing wrong if we can't tell what
you're doing.

 

[w]

Hello, sorry about that. Here is the SQL.

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 (3) I am getting the
same number of instructor 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.

Thanks!