OK, this is definitely the time when you want to take a deep breath, and a step back from the problem.
So, each school may have different dates for their half-term beginning and ending. And, all of these dates are in a table. So far, so good.
The long text field somewhat terrifies me. That is a minefield of potential problems. If I were in this situation, I would try to find a way to parse that text field into a table structure, before I ever began trying to process against it. But, I understand that such a solution is not always viable.
So, here's what you do. You need to use a fairly complicated join here. I know how to do it in SQL, so that's what I'll run with. I know that it
can be done in Crystal with linking. But, I'm highly prejudiced towards SQL, so I'd have to research the linking solution.
SELECT OrigQuery.*, TermDates.*
FROM OrigQuery
JOIN TermDates
ON OrigQuery.School = TermDates.School
AND OrigQuery.AttendDate BETWEEN TermDates.ht-start AND TermDates.ht-end
In this statement, OrigQuery is the original query you are using, with the process to extract the data from the text field. TermDates is the table that contains all the dates for the schools. I'm presuming some about the record structure here. Namely, that there is a record in that table for each half-term for each school, with beginning and ending dates. I'm also making the assumption that you can tell it's the first half-term if "term-start" equals "ht-start."
Now that you have that query, you have the ability to note for each record which half-term it falls into. From that point, it becomes a pretty straightforward exercise to group it by term, school, and/or student.