Need a consecutive date total Report from one date field in table.

[Guest]

I am at a loss on this one. I am able to create a consecutive day total query
when I have for example multiple fields to calculate "date entered" & "date
completed" , but I have a new one I can not get.

Is there a way to complete a report or query equation to show total
consecutive days from one field? example employee absent on 1/1/06 next
entry employee absent 1/2/06 = 2 consecutive days.

 

[Guest]

Need more information to help on this one. Is there a record for every day?
Or a record for everyday absent? In any case you'll probably need some code
to process record by record.

 

[Guest]

Its a record for every absence. For example:

Employee Type of Absence Date

 

[Guest]

I think it would be straightforward if you processed the data and added
another field to each record: absenseNum
in pseudocode:

create a recordset sorted by employee and date
set previousRecEmployee and previousRecDate to values on first record
increment absenseCounter
set absenseNum on first record to absenseCounter
loop thru records 2..end
if currentRecEmployee = previousRecEmplyee and
currentRecDate = previousRecDate + 1Day Then

set currentRec.absenseNum = absenseCounter
Else
increment absenseCounter
set currentRec.absenseNum = absenseCounter
EndIf

set previousRecEmployee and previousRecDate to values on current record
NextRecord


After that, you can create a query that groups on employee and absenseNum;
If the table named absent has fields employee, absenseNum, date
the query would be

SELECT absent.employee, absent.absenseNum, Min(absent.date) AS [Date],
Count(absent.date) AS Days
FROM absent
GROUP BY absent.employee, absent.absenseNum;