Continuous records

[wrightyrx7]

Hi all,

I have a table with dates of sickness for employees.

However no 'one' record can cross over into the following month like 25/01/2016 - 05/02/2016. So they are recorded to the end of the month then a new record starts from 1st of the following month.

Example:
If an employee had records from '01/01/2016 to 31/03/2016' and '01/05/2016 to 31/05/2016' it would have records as follows:-
EmployeeID | StartDate | EndDate
11111 | 01/01/2016 | 31/01/2016
11111 | 01/02/2016 | 29/02/2016
11111 | 01/03/2016 | 31/03/2016
11111 | 01/05/2016 | 31/05/2016


I need it to show in a query as:-
EmployeeID | StartDate | EndDate
11111 | 01/01/2016 | 31/03/2016
11111 | 01/05/2016 | 31/05/2016

then move onto the next EmployeeID.

Can anyone please help..please please please.

Thanks in advance
Chris

 

[Ranman256]

since you don't care about the days (you know what they are), query on the month

select empID, min(month(startdate)), max(month(startdate))
(then if you want, tack on the days start &end)

 

[wrightyrx7]

since you don't care about the days (you know what they are), query on the month

select empID, min(month(startdate)), max(month(startdate))
(then if you want, tack on the days start &end)

Thanks Ranman i will give this a go.

When you say i dont care the days though, what do you mean? Because once the data comes out i need to calculate the working days (Monday - Friday) between the periods. Or i could maybe do this before and total the working days field?

Thanks again Chris

 

[wrightyrx7]

Thanks Ranman i will give this a go.

When you say i dont care the days though, what do you mean? Because once the data comes out i need to calculate the working days (Monday - Friday) between the periods. Or i could maybe do this before and total the working days field?

Thanks again Chris

I give it a go but it doesnt come out as i wanted. It gets the MIN and MAX dates but if there is a gap between records it should be a new record (different period of absence.)

Thanks
Chris

 

[gemma-the-husky]

out of interest, why not cross the month end boundary?

logically, it would be far easier to have a single absence record with

"start of absence", "end of absence"

given what you have, I think you would need to iterate a record set to identify continuous absences over month ends.

 

[wrightyrx7]

out of interest, why not cross the month end boundary?

logically, it would be far easier to have a single absence record with

"start of absence", "end of absence"

given what you have, I think you would need to iterate a record set to identify continuous absences over month ends.

Hello,

The software we use will not let the data be input across the month end boundary. Therefore when I pull the data 'out' into Access via Oracle SQL it comes out split in the individual months.

However for sickness monitoring and for it to be easier to read the data, it would be better in a single absence record.

 

[arnelgp]

this can be done through VBA and using a temporary table.

 

[wrightyrx7]

this can be done through VBA and using a temporary table.

I have done it in the past via Excel VBA but wouldnt know how to do it in Access VBA. The code I wrote in Excel was

Sub SickDates()

Dim lRow As Long, i As Long

With ActiveSheet
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = lRow To 2 Step -1
        
            If Range("A" & i).Value = Range("A" & i - 1).Value Then
                If DateAdd("d", -1, Range("J" & i - 1).Value) = Range("K" & i).Value Then
                    Range("J" & i - 1).Value = Range("J" & i).Value
                    Cells(i, "A").EntireRow.Delete
                End If
            End If
        Next i
End With
MsgBox ("All dates updated")

End Sub

 

[wrightyrx7]

I still havnt figured this out if anyone can help.

Still doing it manually

 

[arnelgp]

what is the name of the table?

 

[wrightyrx7]

what is the name of the table?

Thanks for your reply, it is - SickRecs

 

[arnelgp]

copy and paste in a module.
this will create a temporary table zzSickRecs table.
not yet thoroughly tested.

Public Sub sbAttendance()

    Dim td As dao.TableDef
    Dim db As dao.Database
    Dim rsSource As dao.Recordset
    Dim rsTarget As dao.Recordset
    
    Const TABLENAME As String = "SickRecs"
    
    Set db = CurrentDb
    On Error Resume Next
    Set td = db.TableDefs("zz" & TABLENAME)
    If Err.Number <> 0 Then
        db.Execute "select * into zz" & TABLENAME & " from " & TABLENAME & " where (0=1);"
    Else
        db.Execute "delete zz" & TABLENAME & ".* from zz" & TABLENAME & ";"
    End If
    set td = Nothing
    On Error GoTo 0
    
    Set rsSource = db.OpenRecordset("select * from " & TABLENAME & " order by employeeID, startDate;", dbOpenSnapshot)
    Set rsTarget = db.OpenRecordset("zz" & TABLENAME, dbOpenDynaset)
    
    With rsSource
        If Not (.BOF And .EOF) Then .MoveFirst
        Do While Not .EOF
            Dim varID As Variant
            Dim dteStart As Date
            Dim dteEnd As Date
            Dim bolFirst As Boolean
            Dim i As Integer
            varID = ![employeeid]
            dteStart = ![StartDate]
            dteEnd = ![EndDate]
            bolFirst = True
            i = 1
            .MoveNext
            If .EOF Then Exit Do
            dteEnd = ![EndDate]
            Do While ![employeeid] = varID
                If Month(![StartDate]) <> (Month(dteStart) + i) Then
                    rsTarget.AddNew
                    rsTarget![employeeid] = varID
                    rsTarget![StartDate] = dteStart
                    rsTarget![EndDate] = dteEnd
                    rsTarget.Update
                    Exit Do
                Else
                    dteEnd = ![EndDate]
                    i = i + 1
                End If
                .MoveNext
                If .EOF Then Exit Do
            Loop
        Loop
        rsTarget.AddNew
        rsTarget![employeeid] = varID
        rsTarget![StartDate] = dteStart
        rsTarget![EndDate] = dteEnd
        rsTarget.Update
        
        .Close
    End With
    rsTarget.Close
    Set rsSource = Nothing
    Set rsTarget = Nothing
    Set db = Nothing
End Sub

 

[wrightyrx7]

copy and paste in a module.
this will create a temporary table zzSickRecs table.
not yet thoroughly tested.

Public Sub sbAttendance()

    Dim td As dao.TableDef
    Dim db As dao.Database
    Dim rsSource As dao.Recordset
    Dim rsTarget As dao.Recordset
    
    Const TABLENAME As String = "SickRecs"
    
    Set db = CurrentDb
    On Error Resume Next
    Set td = db.TableDefs("zz" & TABLENAME)
    If Err.Number <> 0 Then
        db.Execute "select * into zz" & TABLENAME & " from " & TABLENAME & " where (0=1);"
    Else
        db.Execute "delete zz" & TABLENAME & ".* from zz" & TABLENAME & ";"
    End If
    set td = Nothing
    On Error GoTo 0
    
    Set rsSource = db.OpenRecordset("select * from " & TABLENAME & " order by employeeID, startDate;", dbOpenSnapshot)
    Set rsTarget = db.OpenRecordset("zz" & TABLENAME, dbOpenDynaset)
    
    With rsSource
        If Not (.BOF And .EOF) Then .MoveFirst
        Do While Not .EOF
            Dim varID As Variant
            Dim dteStart As Date
            Dim dteEnd As Date
            Dim bolFirst As Boolean
            Dim i As Integer
            varID = ![employeeid]
            dteStart = ![StartDate]
            dteEnd = ![EndDate]
            bolFirst = True
            i = 1
            .MoveNext
            If .EOF Then Exit Do
            dteEnd = ![EndDate]
            Do While ![employeeid] = varID
                If Month(![StartDate]) <> (Month(dteStart) + i) Then
                    rsTarget.AddNew
                    rsTarget![employeeid] = varID
                    rsTarget![StartDate] = dteStart
                    rsTarget![EndDate] = dteEnd
                    rsTarget.Update
                    Exit Do
                Else
                    dteEnd = ![EndDate]
                    i = i + 1
                End If
                .MoveNext
                If .EOF Then Exit Do
            Loop
        Loop
        rsTarget.AddNew
        rsTarget![employeeid] = varID
        rsTarget![StartDate] = dteStart
        rsTarget![EndDate] = dteEnd
        rsTarget.Update
        
        .Close
    End With
    rsTarget.Close
    Set rsSource = Nothing
    Set rsTarget = Nothing
    Set db = Nothing
End Sub

Worked exactly as it should!! Many thanks, however you code has made me discover some admin have been inputting data incorrecty.

We have records where one ends mid month and the next records carries on from the next day... e.g


EmployeeID | StartDate | EndDate
11111 | 01/01/2016 | 20/01/2016
11111 | 21/01/2016 | 31/02/2016

This is still continuous and should be joined.

However i cannot figure out how to ament this part of your code to take this into account.

If Month(![StartDate]) <> (Month(dteStart) + i) Then

It would need to be something like

If ![StartDate] <> dteEnd) - i) Then

but just above this line you change dteEnd = ![EndDate]

I know this is not what was asked for originally but any help would be great.

Thanks again for you help, really appreciate the time you have spent writing this code.

 

[arnelgp]

if that the case don't worry, only reflect the changes to this line:

Set rsSource = db.OpenRecordset("select * from " & TABLENAME & " order by employeeID, Format(startDate,'yyyymmyy') & Format(enddate,'yyyymmdd');", dbOpenSnapshot)

 

[wrightyrx7]

if that the case don't worry, only reflect the changes to this line:

Set rsSource = db.OpenRecordset("select * from " & TABLENAME & " order by employeeID, Format(startDate,'yyyymmyy') & Format(enddate,'yyyymmdd');", dbOpenSnapshot)

Thanks Arnelgp,

I could follow what your original code was doing but not sure about this change. The data isnt coming out as it should. Is it possible the format - Format(startDate,'yyyymmyy') should be Format(startDate,'yyyymmdd')?

Thanks again.

 

[arnelgp]

good catch.