Consecutive Dates 1

[MichaelintheUK]

Please I need to produce output to a query or temporary table based on whether appointments were one offs or covered consecutive days where consecutive days equals 1 appointment.

AA 21/06/2005
AA 22/06/2005
AA 24/06/2005
BB 23/06/2005
CC 24/03/2005
CC 25/03/2005

The result as output should be

AA 2 (i.e. 1 2 day appointment)
AA 1
BB 1
CC 2

I have tried to put together a module but it gives unpredictable results.

thanks

Michael

 

[Remou]

How about:

Sub CreateAptTemp()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strClient As String, strSQL As String
Dim dteDate As Date
Dim intCount As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * From tblAppts Order By Client,Appointment")

If IsNull(DLookup("Name", "MSysObjects", "Name='tblTemp'")) Then
    strSQL = "Create Table tblTemp ( Client Char (50), ApptCount Int )"
    db.Execute strSQL
End If

intCount = 0
Do While Not rs.EOF
    strClient = rs!Client
    Do While rs!Client = strClient
        dteDate = rs!appointment
        rs.MoveNext
        intCount = intCount + 1
        strSQL = "Insert Into tblTemp Values ('" & strClient & "'," & intCount & ")"
        If rs.EOF Then
            db.Execute strSQL
            'DoCmd.OpenTable "tblTemp"
            Exit Sub
        End If
        If rs!Client = strClient Then
            If rs!appointment - dteDate > 1 Then
                db.Execute strSQL
                intCount = 0
            End If
        Else
            db.Execute strSQL
            intCount = 0
        End If
    Loop
Loop
End Sub

 

[MichaelintheUK]

Hello Remou

Many thanks for your suggestion.

thanks

Michael