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.

 

[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