Dropdown list based on a query?

[bilakos93]

Hi all

I have a table that includes names of students.
Duration of studies is 4 years and each year new students come in and others graduate.
There is a table (students) with everyone's names, last names and unique IDs
I have then created a query that runs every time I want to create a report that includes only the students that are studying in a given year
Example: student1 entered in 2018, graduated in 2022. They will be included in the report of 4/5/2019 but not in the report of 4/5/2023.
Let's call the query students_curr
This way, each year only the new students (along with their entry year) will be added on the table without the need to make any further changes.
There is a form on which people add grades etc for the students for different subjects. Let's call this form grades (along with the table). There is a dropdown list from which people select the student they want to enter data for
My question is
Is there a way to include only the records of the query students_curr on the dropdown list (based on the current date)?

Thank you

 

[MajP]

Lets assume your school year goes from Aug to May. So a student graduating in may 2024 is not there in Sep 2024 so you have to account for that. If you stored the date it would be simpler

Select StudentID, StudentLastName, StudentFirstName, OtherStudentInfo...... Where GraduationDate <= Date()

If not storing the date but only graduation year you could
Select StudentID, StudentLastName, StudentFirstName, OtherStudentInfo...... Where GraduationDate <= DateSerial([GraduationYear],5,31)

 

[XPS35]

I assume reality is more complicated than you describe here. Students may have to repeat a year, leave in the meantime, or start in a higher grade. So just looking a start year won't give the right results.

 

[Pat Hartman]

There are better ways to do this. The attended periods should be individual rows in a child table. Logically, in the US, the school year starts in September (sometimes August) and goes through the following May or June and is broken into two semesters, Fall and Spring. Some schools offer a summer session which goes from the end of the Spring session until the beginning of the Fall session and would be considered to be part of the preceding school year. So school year 2022 starts in September of 2022 and goes until the end of the Summer session. Even though Spring and Summer happen in 2023, they are logically part of 2022. So, you have to decide how your school years are broken and how many sessions are there. Do you start in January and have four semesters? or three? or only one? That will dictate how you want to identify the Semesters table. So, if a student was present at some time in a semester, there will be a row in the table for that semester. So, if the table has a SchoolYear field and a Semester field, you can use the SchoolYear field to determine if the student attended classes at any time during that year. Or you can count by Semester, whatever makes sense. BUT a date range is not a logical way to log this data.

The strange thing about this is that graduation year is always the end year so if you graduated in June of 23, that would be your graduation year even though June of 23 was part of school year 22. Who said any of this had to make sense. The logic of the school year goes back to the time in our history when most people lived on farms and all the kids were needed at home for planting and harvesting.

 

[GaP42]

Fundamentally it appears your database design is flawed if you have one table holding records of the names of students in each year in which they are enrolled. Databases are designed to minimize redundant data.
This is basically accomplished through the application of database normalization techniques. Basically making sure that data held in tables relates to one "Entity" eg the student, another entity for enrolment. A studentId will identify a student will have a name, DoB etc (and other attributes as required for your application), while enrolment records will have an enrolment year name, enrolment year start date and studentID, where EnrolmentID is the Primary Key of the table, and all attributes of the table relate directly to the PK - as in they depend upon the key and nothing but the key.
Students will have a Student record and a number of enrolment records - as many as needed to correspond to the years in which they were enrolled.
As students may leave prior to graduation within the year they would be expected to graduate, you probably need to be able to record a leave date, and use this to exclude these records in the graduation query?

This is a simplified scenario. To maintain student enrolments and student records, depending on your needs, may involve many more attributes and supporting tables for, say, alias names, change of address history, variations in the start and end of enrolment years, etc