Compare Sickness End Date to Next Sickness Start Date

[Dave C]

Hi I have been asked to do some complex analysis on absence management. The
problem is our HR System reports absence by each week. So for example someone
who has been off for 5 weeks will have 5 different records, but these are
continuous (eg.1st record starts on Monday and finishes Sunday, 2nd record
starts Monday and so on).

I want to analyse the data in Access, but is there any way I can determine
if the absence is continuous - in otherwords if the difference between the
1st record end date and the 2nd record start date is 1 then show the absence
as one period, and so on.

Not everyone will have more than one record so is there an easy way to
identify those staff with multiple records?

Any help will be greatly appreciated! Thanks

 

[Access101]

A totals query with Count > 1 would identify the records.

And the DateDiff function = 7 would help with the gaps