Question for "Marshall Barton"

[w]

Hi Marshall, this is the person that received help from
you from Jan 26 - 30 with the post title:"Query + Report."
Everything has been working fine since the last bit of
advice that you gave me, however I found something in my
report results that I had not seen before. If you recall
I was trying to count the number of students in the class
as well as the number of unique instructors. This was
working well until I set up my query to prompt the user to
enter a start date as well as an end date. The problem is
that even though the count of instructors is still unique,
it counts all of the instructors within each group and
does not take into account the date limitation. This is
what the new sql statemtent looks like for the first query
with the dates added (before the union query that was
created using this query):

SELECT Courses.courseID, Courses.level, Count(Courses.cid)
AS CountOfcid, Sum(Courses.studcount) AS SumOfstudcount,
(SELECT Count(*) FROM Instructor WHERE Instructor.InsId
IN (SELECT InsId FROM Courses AS X WHERE X.courseID
Courses.courseID AND X.level=Courses.level)) AS
CountOfInsId FROM Courses
WHERE (((Courses.date) Between [start] And [end]))
GROUP BY Courses.courseID, Courses.level;

The union query after that was the same as what you had
told me before. Here is an example of the results that I
am getting:

Course level Countcid Studcount InsCount

English 101 5 150 9

as you can see there were five classes taught so at most
the number of instructors would be 5 and it is counting
all of the unique instructors in the category without
taking into account the date.

Is there an easy solution to this?
Thank you very much for your time

 

[Marshall Barton]

Hi Marshall, this is the person that received help from
you from Jan 26 - 30 with the post title:"Query + Report."
Everything has been working fine since the last bit of
advice that you gave me, however I found something in my
report results that I had not seen before. If you recall
I was trying to count the number of students in the class
as well as the number of unique instructors. This was
working well until I set up my query to prompt the user to
enter a start date as well as an end date. The problem is
that even though the count of instructors is still unique,
it counts all of the instructors within each group and
does not take into account the date limitation. This is
what the new sql statemtent looks like for the first query
with the dates added (before the union query that was
created using this query):

SELECT Courses.courseID, Courses.level, Count(Courses.cid)
AS CountOfcid, Sum(Courses.studcount) AS SumOfstudcount,
(SELECT Count(*) FROM Instructor WHERE Instructor.InsId
IN (SELECT InsId FROM Courses AS X WHERE X.courseID
Courses.courseID AND X.level=Courses.level)) AS
CountOfInsId FROM Courses
WHERE (((Courses.date) Between [start] And [end]))
GROUP BY Courses.courseID, Courses.level;

The union query after that was the same as what you had
told me before. Here is an example of the results that I
am getting:

Course level Countcid Studcount InsCount

English 101 5 150 9

as you can see there were five classes taught so at most
the number of instructors would be 5 and it is counting
all of the unique instructors in the category without
taking into account the date.

You need to use the criteria in the subquery too:

.... IN (SELECT InsId
FROM Courses AS X
WHERE X.courseID = Courses.courseID
AND X.level=Courses.level
AND Courses.date Between [start] And [end]
) AS CountOfInsId
....