consecutive dates

[gurutrue]

I have a table with two fields EmployeeID and AbsenceDate. I have another table that has one field Holidays.

What would be the best way to determine the number of consecutive dates that were taken?

Something like:

EmployeeID ConsecutiveAbsences
HJ745278 2
HF969699 1

 

[gurutrue]

I found some VBA code which I have modified to suit my purpose. It functions mostly correctly and outputs data into the Immediate window. Now I am not a VBA pro by any means. Can anyone point me in the right direction to get the data to output into a table within Access or output to a text file.



Public Function Consecutive()
Dim db As Database
Dim rs As Recordset
Dim n As Integer
Dim AStartDate As Date
Dim sql As String
Dim MyText As String
Dim MyNum As Integer

sql = "SELECT EmployeeID, AbsenceDate " & _
"FROM Employee_Absences_UNION " & _
"ORDER BY EmployeeID, AbsenceDate;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
rs.MoveFirst
Do Until rs.EOF = True
n = 0
AStartDate = rs.Fields("AbsenceDate")
Do Until rs.EOF = True
If rs.Fields("AbsenceDate") = DateAdd("d", n, AStartDate) Then
n = n + 1
rs.MoveNext
Else
Exit Do
End If
Loop

rs.MovePrevious
Debug.Print rs.Fields("EmployeeID") & "," & AStartDate & "," & rs.Fields("AbsenceDate")
rs.MoveNext
Loop
Set db = Nothing
Set rs = Nothing
End Function