Notify after two consecutive absentes (1 Viewer)

[Dazzy]

Hangover not with standing but I am still having real difficulty with this.

Student A

Attendance Date​

Status​

11/08/2014​

Absent - Unexcused

13/08/2014​

Present

14/08/2014​

Absent - Unexcused

15/08/2014​

Absent - Unexcused

18/08/2014​

Present


The student has 3 Absent - Unexcused records so the count will return 3, Min Date will return 11/08/14 and Max Date 15/08/14. Min date surely will always be their first Absent - Unexcused date.

So with this in mind has more attendance records are added ie

Attendance Date​

Status​

11/08/2014​

Absent - Unexcused

13/08/2014​

Present

14/08/2014​

Absent - Unexcused

15/08/2014​

Absent - Unexcused

18/08/2014​

Present

19/08/2014​

Absent - Unexcused

25/08/2014​

Present

26/08/2014​

Absent - Unexcused

29/08/2014​

Present


IE Min Date is still the 11/08/2014 and Max Date is now 26/08/2014, so maybe I am way over thinking this.

I have attached a stripped down version of the database if any kind individual could help this beer ridden individual with an example.

Thanks

 

[vbaInet]

Just had time to look at this properly and it seemed a bit more tricky than I envisaged.

See attached two queries, qryAbsenceTotal and qryAbsenceBreakdown. The former gives you a total count of consecutive absences per student and the other gives a breakdown. Both show the start and end dates per consecutive absences on the same line.

I would imagine this can be further optimised and the fact that your tables aren't properly normalised is adding to the complexity of the solution. I've avoided creating a temp table for this.

 

[Dazzy]

Thanks I'll take a look