Count number of student in specific school year

[Khalil Handal]

I need to count number of students in a specific grade in a specific school year (previous or current).
Note that some of the students left school. In this case the field: DateDeparted is not Null and some students are still in the school with DateDeparted is Null.



Example:
I want to count students in grade six for school year 2021. If the student is still at school he will be now in grade nine and field DateDeparted is Null, while if he left school after the year 2021 the field DateDeparted will be: Is Not Null.

Any ideas?

Khalil

[Welshgasman]

Then date does not come into it, does it?

[Khalil Handal]

Hi,
I Think no.
What criteria should I use?

Khalil

[orange]

How about providing sample data in your table(s) and any queries you have tried?

[Welshgasman]

Hi,
I Think no.
What criteria should I use?

Khalil

However you define your school year?

[Khalil Handal]

Hi,
I defined my school year and I am doing some progress. I will keep all of you posted.
Thank you

[Welshgasman]

If you havce a field that actually defines the school year and perhaps term, like 2021 and 1, then just look for that field being 2021. If you wanted just one term then include that as criteria.
If you group by those to fields then you'd see all the pupils in that year by term.

It all depends on how you the developer have define the school year. Some might just use start and end dates, so you would search between those dates.

At the end of the day, the structure of you DB defines exactly how you would search. From your first post I believe the leaving date does not come into it. If someone leave mid term, they were still in the school in that year?
So YOU have to be absolutely specific as to what you want.

[Khalil Handal]

Hi,
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.

Khalil

[ArviLaanemets]

[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:

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:

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.

[Khalil Handal]

Hi,

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.

Yes: A student might be enrolled in a school year but not in a different school year.
To be more clear in relation to the above quote about enrollments, I have a separate table for school years: tblSchoolYears with pkSchoolYears, SchoolYears fields only. In the enrollmwents i have fkSchoolYears field. The same thing also goes for the grade levels. I do also have a table tblStudents for names of students and other related information.
I will try to apply the above suggested queries and see were it lead me.
Thank you

[ArviLaanemets]

Yes: A student might be enrolled in a school year but not in a different school year.
To be more clear in relation to the above quote about enrollments, I have a separate table for school years: tblSchoolYears with pkSchoolYears, SchoolYears fields only. In the enrollmwents i have fkSchoolYears field. The same thing also goes for the grade levels. I do also have a table tblStudents for names of students and other related information.
I will try to apply the above suggested queries and see were it lead me.

I think you didn't read my post properly. The table structure where for different school years are different columns doesn't work well (really, this structure works very badly). A couple of reasons on fly:
I can think about 2 ways to count students for certain year with your current design.
a) You have to check for every school year column, is it the asked one or not. When yes, you count it, otherwise you ignore it. When the number of school years in your db, increases, the formula to calculate the count in this way will grow too, and very soon will be extremly slow one;
b) You have to create a procedure, which creates a query string for school year you want students to be counted, and tyhen runs it.
With either of ways, you have to redesign the query or procedure whenever you add a new school year into your DB. And redesign the enrollments table, the form where you register enrollments, and probably all other tables, queries, and forms which are in any way using enrollments data.

With alternative design I advised, you can avoid all this hassle!

[Khalil Handal]

Hi,

Instead replace the enrollments table with one like:
tblEnrollments: EnrollmentID, StudentID, SchoolYear, Grade, ...

I have my table of enrollments as you suggested and I build a query based on it with other necessary tables having a criteria of:
Like [Forms]![frmStatistics]![cboSchoolYear]
for selecting the require school year.
I am getting the totals I need.

Thank you