Merge continuous records - Please help

[wrightyrx7]

Hi all,



I have a table with dates of sickness for employees.

However no 'one' record can cross over a month end boundry e.g. 25/01/2016 - 05/02/2016. They are recorded to the end of the month then a new record starts from 1st of the following month (this is not by choice this is due to the software we use).

All the data below is being pulled out the software via SQL in a linked table...

Example:
If an employee had records from '01/01/2016 to 31/03/2016' and '01/05/2016 to 31/05/2016' the software would have records as follows:-
EmployeeID | StartDate | EndDate | Working Days
11111 | 01/01/2016 | 31/01/2016 | 21
11111 | 01/02/2016 | 29/02/2016 | 21
11111 | 01/03/2016 | 31/03/2016 | 23
11111 | 01/05/2016 | 31/05/2016 | 22

What I need is if the employee has a record with a START DATE that is the day after the END DATE of another record then join them together and add the days.

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

then move onto the next EmployeeID.

This is so we can determine if someone has been on long term sick or not.

Can anyone please help..please please please.

Thanks in advance
Chris

[ranman256]

Here is vb code to scan and post the results

'sort the data in the sql, vb code scans list joining the dates
'then posts to a output table2
Public Sub DateJoin()
Dim rst
Dim vEmp, vDate1, vDate2, vDateStart, vDateEnd, vPrevEmp
Dim lTot As Long, lVal As Long
Dim sSql As String
vPrevEmp = ""
sSql = "select * from tbl order by [empID],[StartDate]"
Set rst = CurrentDb.OpenRecordset(sSql)
With rst
    While Not .EOF
        vEmp = .Fields("[empID]") & ""
        lVal = .Fields("[Working Days]")
        vDate1 = .Fields("[StartDate]")
        vDate2 = .Fields("[EndDate]")
        
        If vEmp <> vPrevEmp And vPrevEmp <> "" Then
           vEndDate = vDate2
                '-----------------------
                'post THE total...
                '-----------------------
           sSql = "insert into table2 ([empid],[startDate],[EndDate],TotalDays)  values (" & vPrevEmp & ",#" & vDateStart & "#,#" & vDateEnd & "#," & lTot & ")"
           DoCmd.RunSQL sSql
           lTot = 0
           vDateStart = vDate1
        End If
        
        lTot = lTot + lVal
        
        vPrevEmp = vEmp
       .MoveNext
    Wend
End With

[wrightyrx7]

Thank you for your reply.. it seems to work fine until it gets to the "INSERT", i get an error message

"Syntax error in date query expression '#'.

[rpeare]

did you make startdate and enddate in table2 date fields?

you can debug.print ssql after the sql statement is built to see what it's trying to append, it may be a blank date field which you'd have to account for.