Grouping by close Dates

[Jonno]

I have a table which records attendance for staff. Each record represents one day per staff.

NAME ATTCODE DATE etc etc

I want to find a way of grouping the records for a type of attendance code, where records are chronologically sequenced. I wish to find for example how many instances a person was sick, (e.g. sick for one day = once, sick for 3 days in a row = twice) - rather than count how many individual days sick per person.
Appreciate any ideas. I do have a way of doing this but is very long winded with many queries etc.

 

[Tiro Cupidus]

Have you looked at DCount()?

 

[Fizzio]

This would probably be better calculated using a looping recordset.
How I would do this is to use an array (or if pushed a temp table - but this has Db bloat but is easier to analyse data - the choice is yours!) to store the start date of the sickness, the end date and the days off sick (you would not normally store this as it can be calculated but if the sickness spans a weekend, the calculation becomes more difficult).

eg in plain english - too late to code here!

1 open a recordset with that individuals sickness record.
2 start a loop
3 check start date and store this value
4 check next record:
if next date 1 day after previous OR if prev day friday, is the date a monday?
if so, add 1 to days off sick
else store last date off sick and calculate number of days off.
5 loop until EOF

I've included an example of how I used arrays to breakdown a sentence into words of certain lengths. Have a look at the various functions and try to break it down to your needs.

 

[Jonno]

Thanks!