Grouping blocks of dates

[Mmattson]

I couldn't find anything that related specifically to this question.

I have a student attendance table. There is a record for each student, each day regardless of whether the student is Present, Tardy or Absent. I need to group the blocks of absent to count number of consequential days the student is absent. This goes beyond simple grouping because a student may be gone 5 days and if I group on the dates I will get 5 separate groups. I want to get 1 group with a count of 5 occurrences.

For example:

Pupil 123456

3/15/02 Present
3/16/02 Absent
3/17/02 Absent
3/18/02 Absent
3/19/02 Present


I want this to give me a return that will group it like this:

3/16/02 3 days absent

 

[dcx693]

So you want to count the number of days each student was marked Absent? This doesn't sound terribly complicated, unless I'm missing something.

Use a totals query and group by student, then count by the attendance field and set it to display only those records that are equal to "Absent".

If I've missed something or you need more help, write back.

 

[Mmattson]

The Totals won't work because if the student were gone 3 days one weeks then gone 1 day two weeks later, I still only have one group and I have a count of X days, but I am trying to get number of contiguous days. That's the kicker.

The format () function creates the same challenge. I won't be able to get the end result of having a start date of absence and the number of days contiguously absent.

 

[dcx693]

Aha, the continuity problem. You may want to look at this recent thread for ideas: Identifying Connected Track Mileage

 

[Jon K]

Yes, dcx is right. Rather than using a query, you need VBA code to loop through the records and do the counting.