Working out a school term
I have a fikemaker database, where I need to work out which term it is in one of my Tables using a calculation. One table - called "Term Dates" has the fields: Record ID (Unique field), Year, Term, Start Date and Finish Date. These details are entered into this table at the begginning of each school year. Each year has only four terms. Each term dates do not overlap.
In another table (not related) I have the Unique ID, Student ID, Year, Term and Date Recorded. I am easly able to calculate the Year by using the "Year (Date Recorded)" calculation. What I am not sure how to do is to calculate what the term is?
I thought using the "Case" calculation might work, but just can't see how to get it formualted. I essentially want to take the "Date Recorded" field, and see which date range it falls between inside the "Term Dates" table. That is which row the "Date Recorded" field falls between the "Start Date" and "Finish Date" fields. Once it finds this, I want it to provide the Term value (which is in the same row) back as the calculation result.
If the date does not fall between any of the dates, I would like it to return the value 0 (which just tells me it didn't find anything) The Term values it returns are 1, 2, 3 or 4 as we have four terms a year.
Any help would be appreciated.