Dates

[sdc1234]

Greetings,

I have a list of classes completed by students, the list is for past and future classes, is there a script I can write that states that even though they are scheduled to take the class, but for a future date, they will still pop up on the roster that I create.



v/r
novice

[June7]

What exactly do you need? Just because student has taken classes doesn't prevent listing student in a combobox.

[sdc1234]

I import a student roster that I receive and it has people set to take class next month, it is set to default on their end as passed even though they have yet to take the course, so when I import it in and try to pull a roster it needs to populate those names, I keep thinking bout an IIF(startDate<=Now(),0,1), but that would not give me the whole thing...

[June7]

You need to pull the roster for a class for a particular date (or before current date)? Don't include the passed/notpassed field as filter criteria.

Why would date criteria not give you 'whole thing'? I don't know your data structure and don't understand the issue.

You want a roster of what?

[sdc1234]

A roster of students who need the course, the import has a list of students who are scheduled but have not yet taken the course

[June7]

How do you know which students need course - all of them?

This requires a dataset of all possible student/course pairs. This can be created with a query that pulls from Students and Courses. Do no use JOIN clause and the output will be a Cartesian relation of all records in each table to all records in other table.

SELECT StudentID, CourseID FROM Students, Courses;

If you want to know which students are not yet registered for courses, join that query to the dataset of student registration, link on both common fields (a compound join). JOIN type of "Include all records from StudentCourses and only those from Registration that match".

Set filter criteria of Is Null under the Registration table CourseID field.

If you also want student names and course names, include those tables in the query as well.