Based on the information you've given, I would probably have a "SemesterT" structured something like the following:

SemesterT
SemesterID autonumber primary key
SemesterName Short Text
StartDate DateTime
EndDate DateTime
Notes Long Text

Then I would add a LastSemesterID of type Number -> Long Integer to whatever table holds your teacher information so it would be easily updateable in a form.

Finally, I would create a query in the QBE that pulls in the teacher's information from the teacher table and semester information in from the semester table. That query would have a calculated field that determined if their last semester ended within the last 18-months. If so, then set their IsActive flag to yes, if not, no. You could do it from the semester start date, but it would require more calculations.

The QBE could do most of the heavy lifting so you don't have to write any SQL, but you'd still have to understand Date Math to create the calculations. Richard has many other videos on the topic we can point you to if necessary.

Or to more specifically help you build it, we'd need to know more about your table structure, field names, etc. so we can help you create the calculations.

Daniel  It depends on how exactly you measure those "18 months."  If a school year comprises of 4 semesters, do you see "18 months" as one-and-a-half calendar year or one-and-a-half school year?  The latter would be 6 semesters, which could be longer than 18 months due to irregular start dates and lengths of semesters.  Suppose a professor most recently taught on 8/12/26, the last day of the Summer 2026 term.  But the previous time, he taught on 1/21/25, the first day of the Spring 2025 term.  That was 6 semesters earlier, but it was also 568 days, or 18 months 22 days earlier.  It was outside the 18-month window, but inside the 6-semester window.  It is up to your school's policy to decide if this is inside the "active window" or not.

It really comes down to whether you need to retain the teaching history.

If you do, keep a table of teaching assignments with the applicable date or semester-ending date. Then you can use DMax to determine the most recent date a professor taught and compare that to DateAdd("m", -18, Date()).

If all you need is their current active status, keep it simple and store the LastTaughtDate in the professor's record. Update it whenever they teach, and the active calculation becomes very straightforward.

Do not store an IsActive flag at all, the 18 month window moves forward every day, so any saved flag would become old, and it relies on remembering to update it. Instead, calculate active status when needed. The easiest way to do that is to keep a teaching history, one record for each semester a professor taught, along with a link to a semester table with that semester's start and end dates, and to use the end date of the semester rather than the course's date. This would also solve the Fall '26 started September 9th problem, as dates are saved once per semester. A professor is active if their most recent semester end date is within the last 18 months, and they have paid dues up to today. Since that depends on dues, you might also want to store dues payments in a separate table. And because the history is saved, you can recalculate the list as of any date in the past, which may be useful for things like auditing or voting purposes. And as noted by Kevin, you need to decide whether you mean 18 months as in calendar months or six semesters.