[QUOTE=Khalil Handal;524069]...
The school year is actually defined as 2020-2021 and can be selected from a combo box.
I Added a field CurrentStudent a Yes/No field for table Enrollments which shows the records for all school years for all students and I used that field as criteria (True) to get my results.
/QUOTE]
No info about your table structures provided jet, so I can only quess.
Based on your post, you have a table for enrollments with structure like:
tblEnrollments: EnrollemntID, StudentID, ShoolYear1, SchoolYear2, ..., ShoolYearN, where the SchoolYear# fields are true, when the student was enlisted for this school year, and False or empty, when the student wasn't enlisted. When this is so, then you are on your way into world of hurt for as many years as you use this DB!
Instead replace the enrollments table with one like:
tblEnrollments: EnrollmentID, StudentID, SchoolYear, Grade, ...
For every school year the student is enlisted, there will be a record in tblEnrollments. When the student wasn't enlisted for specific school year, there will be no record for this student in tblEnrollments.
Having the Grade info in same table allows to keep the info compact eand easily accessible.
I assume you have also tables where all students are registered and any personal info about them is stored, like
tblStudents: StudentID, ForName, LastName, ...
And a table where school years are registered (and is used as source for combo you mentioned), like
tblSchoolYears: SchoolYear
With structure like this, your query will be like:
Code:
SELECT enr.Grade, Count(enr.StudentID) As StudentsCnt
FROM tblEnrollments enr
WHERE enr.SchoolYear = YourSchoolYearComboValue
Group By enr.Grade
To get a list of all students in specific grade in specific school year, you use a query like:
Code:
SELECT stu.ForeName, stu.LastName, ...
FROM tblEnrollments enr Inner Join tblStudents stu ON stu.StudentID = enr.StudentID
WHERE enr.SchoolYear = YourSchoolYearComboValue AND enr.Grade = YourGradeComboValue
Etc.