Count Distinct Schools for Student

How can I count the total number of distinct schools a student will be attending if they will attend multiple schools and do multiple courses throughout their education in one query?

Here is what I'm doing:

1st Query = [* CountStudentSchools_01]
----------------
SELECT StudentCourse.StdNo, StudentCourse.SchoolId
FROM StudentCourse
GROUP BY StudentCourse.StdNo, StudentCourse.SchoolId
ORDER BY StudentCourse.StdNo;


2nd Query
---------------
SELECT [* CountStudentSchools_01].StdNo, Count([* CountStudentSchools_01].SchoolId) AS CountOfSchoolId
FROM [* CountStudentSchools_01]
GROUP BY [* CountStudentSchools_01].StdNo
ORDER BY [* CountStudentSchools_01].StdNo;


The reason I didn't just do the count in the 1st Query as follows:
----------------
SELECT StudentCourse.StdNo, Count(StudentCourse.SchoolId) AS CountOfSchoolId
FROM StudentCourse
GROUP BY StudentCourse.StdNo
ORDER BY StudentCourse.StdNo;


...is because some students do several different courses at the same school, which means I get a duplicate count of some schools is this situation. The 2nd Query returns the correct count.

How can I do this is one query?
Start Free Trial