We use CMIS and teachers register their classes every lesson. At the moment we don't have a simple way of finding out if students have been present for part of the day, but absent for one or more of the periods. In other words, have they nipped off for an unscheduled break during the day...

As an example, I'd like it to find Bob, who was present periods 1, 2, 3 and 5 (missing p4) and Fred, who was present p1-4 but absent 5; however I'm not interested in Bill, who didn't get to school until later and was present p2-5 but missing p1.

I did set up a spreadsheet for one of the office staff to export all the data to, then press a button, but I can't believe there isn't a ready made report for this.

Does anyone know how to do this "at the push of a button" rather than going and looking at individual student records? I'm even happy to use an SQL query if someone can point me at the right tables (yes - I've had a brief glimpse under the bonnet and wept at the prospect).

This seems like such an integral feature of lesson by lesson registration that I just know there must be a simple answer...