Merge continuous records

[wrightyrx7]

Hi all,

I have a table with dates of sickness for employees.

However due to the system we use 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 sick records from '01/01/2016 to 31/03/2016' and '01/05/2016 to 31/05/2016' it would have show 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

 

[Joe4]

That is going to be pretty tricky. Here is how I would probably approach it.

Use RecordSets and VBA and write the results to a new table. You would need to sort the data by EmployeeId and StartDate. Then using Recordsets in VBA, loop through your data, and compare the EmployeeID to the previous record, and if it is the same, compare the StartDate of the current record to the EndDate of the previous record. If the difference is one, those records can be combined. If not, just write the current record out to the new table.

In the situation where the record can be combined, you will need to check the next record to, to see if that can also be combined before writing it out to the new table.