Notify after two consecutive absentes

[Dazzy]

Hey All

I have a Student Attendance table and it would be great if I could get some way of automatically determining if the attendance status is Absent - Unexcused for two or more consecutive sessions in order for someone to get in contact with the student.

The table makeup is as follows.

Anyone any ideas, its only 7am here and my head is fried lol

Thanks

 

[Dazzy]

Hey

The status field can be set to any of the following

Absent - Excused
Absent - Unexcused
Present
Tardy

 

[Dazzy]

Yes if a student notifies us that they cant come in we count that as Absent - Excused, which allows us to offer the space to someone else if they need an extra session. The goal is to keep occupancy at around 95% in class rooms.

 

[Dazzy]

Hi

Yes dates may not be consecutive, as for instance someone might have a session on a Monday and then not again till Wednesday.

We offer a very flexible approach to education for adults so they can fit it into their lives but it leaves this sort of stuff overly more complicated.

Regards

 

[Dazzy]

Hi

No as say if a tutor is off on holiday another one will cover the class.

Many thanks

 

[vbaInet]

If I could chime in, you need two determine a couple of things:

1. A count of "Absent - Unexcused" per student
2. The Min and Max date of "Absent - Unexcused" per student
3. A count of records between Min and Max (from step 2) per student
4. A comparison of 1 against 3 ensuring that the counts are equal.

 

[Dazzy]

Hey

This is where I normally come confused, rightly or wrongly I try to do this sort of things on a report initially, but I get lost very quickly as my vba isn't great.

Would I not need to reset the count if a student has been present again?

 

[vbaInet]

There's no VBA involved, all done in a query. Just take it one step after the other.

You're not resetting anything as it's all done in a query. Start with the first step.

 

[Dazzy]

I still don't see how this will work.

If someone had status: Absent - Unexcused for Monday and Wednesday last week, but for Tuesday they had status: Present, surely the min/max wont take that into account? because the min date would be the 11/08/14 and the max 13/08/14, count being 2 but these are not consecutive.

Maybe I am missing something entirely here, sorry if it's my just my lack of knowledge here.

 

[vbaInet]

If someone had status: Absent - Unexcused for Monday and Wednesday last week, but for Tuesday they had status: Present,

Then that's not consecutive is it? Consecutive is Monday and Tuesday in the same week.

 

[Dazzy]

Yes but surely the count would still be 2 as the Min date would have been Monday and Max date Wednesday?

 

[vbaInet]

What I described is supposed to be for consecutive absence.

If all you want is a count of absence and to check that there has been at least 2 occurrences within a given period, then we can advise differently.

 

[Dazzy]

What I need to know is this

If a student has attendance status: Absent - Unexcused for 2 or more consecutive sessions, it will notify us, these sessions may not be on consecutive days. IE they could have sessions on Monday, Wednesday and Friday, some might have only one session per week etc.

I am having a real hard time visualising things today think I need a drink lol

 

[vbaInet]

If a student has attendance status: Absent - Unexcused for 2 or more consecutive sessions, it will notify us, these sessions may not be on consecutive days. IE they could have sessions on Monday, Wednesday and Friday, some might have only one session per week etc.

Yes that's right and that's what the SQL I explained in my first post does. To help clear things up, I'll give a scenario:

1. Student A has one session per week (i.e. Monday)
2. The student missed the sessions on 4 Aug and 11 Aug
3. The Min is 4 Aug and the Max is 11 Aug
4. You count how many records exist between 4 Aug and 11 Aug. There should be only 2 meaning it's consecutive.

Remember we're not counting how many days, we're counting how many records (or sessions) exist.

Another scenario:
1. Student A has one session per week (i.e. Monday)
2. The student missed the sessions on 4 Aug and 18 Aug, but attended the session on 11 Aug
3. The Min is 4 Aug and the Max is 18 Aug
4. You count how many records exist between 4 Aug and 18 Aug and there are 3 meaning this isn't consecutive.

... I need a drink lol

It's not even Friday yet

 

[Dazzy]

Ok that makes sense, mind you I am on pint 4 now, so probably won't tomorrow lol

 

[vbaInet]

Ok that makes sense, mind you I am on pint 4 now, so probably won't tomorrow lol

It must be a blur by now

 

[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