Notify after two consecutive absentes (2 Viewers)

[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

 

[Uncle Gizmo]

How do you determine if the student is absent from the info in that table?

 

[Dazzy]

Hey

The status field can be set to any of the following

Absent - Excused
Absent - Unexcused
Present
Tardy

 

[Uncle Gizmo]

So you only want to test for absent-unexcused?

 

[Uncle Gizmo]

Does consecutive cross a weekend or any other holiday gap?

 

[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

 

[Uncle Gizmo]

Would you need to take into account room or tutor or don't they matter?

 

[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