Find start and end of consecutive dates

[MOOREB]

I am trying to develop a query that will display the start and end dates of a series of dates. My data includes a field with employee number and a field with dates that these employees are absent. I want to display the employee number, the first date that they are absent and the last date they are absent (which may be the same date if they are only away one day). My data looks like this:

EmpID Date
4772 01/01/09
4772 14/04/09
4772 15/04/09
4772 16/04/09
4772 17/04/09
5382 23/03/09
5382 24/03/09
5382 25/03/09
5382 26/03/09
5382 27/03/09
5395 15/06/09

I want the output of my query to look like this:

EmpID StDate EndDate
4772 01/01/09 01/01/09
4772 14/04/09 17/04/09
5382 23/03/09 27/03/09
5395 15/06/09 15/06/09

Any suggestions about how to do this with an Access Query?

Thanks,
Brad

[DonRayner]

You might want to look into using something with the "first of" and "last of" settings for the totals.

[MOOREB]

Don,
This just gives me the first and last values of all of the dates for each individual. For example with employee 4772, I get values of 01/01/09 and 17/04/09 instead of two entries for employee 4772 (one for the single day absence on 01/01/09 and one for the four day absence in April)

You might want to look into using something with the "first of" and "last of" settings for the totals.

[DonRayner]

So let me get this straight.

You want to ouput a start date and end date for each time you have either one day by itself or more than one consecutive day. So if somebody is out on say, july 3, 4, 5, 17,18 29 then the output would be like this

ID Start End
1234 06/03/08 06/05/08
1234 06/17/08 06/18/08
1234 06/29/08 06/29/08

If this is what your after, I can't see any way to do it without doing it in VBA, but maybe someone else will know.

[MOOREB]

Exactly. I think your suggestion put me on the path to finding a possible answer. I included a calculated field for "Weeknumber " (Format([Date],"ww")) in my original table and used it to further group my dates before finding the first and last date within that week. I'm not sure its going to work perfectly for me, as I want to track people who take time off over weekends as well but I may get there yet....

Anyone have any further suggestions?

So let me get this straight.

You want to ouput a start date and end date for each time you have either one day by itself or more than one consecutive day. So if somebody is out on say, july 3, 4, 5, 17,18 29 then the output would be like this

ID Start End
1234 06/03/08 06/05/08
1234 06/17/08 06/18/08
1234 06/29/08 06/29/08

If this is what your after, I can't see any way to do it without doing it in VBA, but maybe someone else will know.

[NeoPa]

I suspect you will need to resort to VBA for this. SQL has no concept of previous and subsequent records.

I certainly see no way to group by the groupings you want (contiguous dates).

[Stewart Ross]

Hi. NeoPa and DonRayner have given you very good advice regarding the non-positional nature of SQL and the potential use of VBA.

A further observation: the data you posted in post # 1 indicates that you are apparently not recording one crucial piece of information in your staff absence table that you really need - the end date of the absence.

Every employee absence has a start date and an associated end date. These may, as you said in post 1, be the same date - but you need to know them both to calculate (for example) the absence duration in days.

I'd suggest that if you don't record the two dates separately at present you really should do so. How else can you know whether an employee's absence is ongoing (where there is a start date but no end date listed), or how many periods of absence have been taken in a defined period of time?

I attach a screenshot of part of an absence recording form from an HR application of mine which shows the principle.

-Stewart