As I said at the start, I was a teacher for 38 years and for the last 15 years or so in charge of data policy and analysis at several schools. Amongst many other things that included managing lates and follow ups to those lates.
There has been a lot of criticism in this thread but it has been directed at what appears to be a poorly thought out policy rather than at yourself. If nothing else, I would urge you to feedback the criticism asking the senior management to review the policy with the aim of creating one that is simple to implement and clear in its aims and objectives.
With your current setup, if I was a problem student with a bit of common sense I would probably be late to 6 periods a day and on time for the other one. One less threshold to be followed up
Honestly, the policy isn't the problem. I posted the policy in another response, you can check it out there.
I haven't see a lot of criticism, mainly people putting their thoughts out there on the way they think this can be solved. There have been so many responses, I haven't even been able to look at all of them yet.
I have seen a couple people who are just rude, but I've just pretty much ignored it. I'm not going to respond their posts anymore.....unless they can solve this problem. haha.
My only frustration is that I feel like I've repeated myself a dozen times trying to explain this loophole.. I guess I'm not explaining it adequately. I'm about ready to give up, but I will give it one more day and only look at responses.
If you were late 6 periods a day, you would be identified the next day when you got 6 more, see the policy. Tardies are cumulative over the course of the semester. I can easily identify students who reach each threshold in the policy. The problem is...students that reach one threshold and proceeds on to the next threshold, on the same day. A student hits the 7th late (tardy) threshold and then two periods later hits the 9th threshold. I can identify the 9th threshold, but I also want to identify that they hit the 7th late threshold and have it show in the query I will use for a report. BTW - remember that we check this every day and try to identify students that hit a threshold, every day. So I also have the priority to pull data for the day and check their "cumulative" tardy total for the semester.
I think I can still use the "between" option that you posted and that will make my product a little better and help out the clerks some too.
I'm really just trying to refine the final product to help our clerks.
My workaround is just to have a query for each threshold. It will still catch each student and the threshold they hit, but it won't show that they've hit the other threshold earlier in the day. It will only show the highest threshold. That's OK, just have to train the clerks to look for that.