consecutive dates and like records

[Jay1978]

Hello All,

I have a table with the below data:

Employee ID	From	To	Location
1	01/01/2010	01/07/2011	Sydney
1	02/07/2011	01/08/2011	Canberra
1	02/08/2011	09/12/2012	Brisbane
1	10/12/2012	15/12/2013	Brisbane
1	16/12/2013	03/03/2014	Canberra
1	04/03/2014	15/05/2014	Canberra
1	16/05/2014	17/11/2014	Canberra
1	18/11/2014	31/12/2014	Canberra
1	01/01/2015	30/06/2015	Brisbane

what i need is for the data to look like:

Employee ID	From	To	Location
1	01/01/2010	01/07/2011	Sydney
1	02/07/2011	01/08/2011	Canberra
1	02/08/2011	15/12/2013	Brisbane
1	16/12/2013	31/12/2014	Canberra
1	01/01/2015	30/06/2015	Brisbane

Please tell me it is possible!!!

[Micron]

what is the logic behind the results? It doesn't seem to be =< 365 days for a location/ID combination, so I don't understand.

[Jay1978]

An employee is assigned to a location. After the initial assignment the employee can be extended.
what I need to show is the start date of the original assignment and where the consecutive date records are at the same location, show the max end date of the final record in the sequence.

[Micron]

I can't think of a single calculated query method because you cannot simply find the min and max of the dates for a person, grouped by location because if they change locations then go back to a previous one, the sequence will be broken. With a small set of persons, I suppose you could perform this aggregate query for each, then combine them with Union queries using the chronological order of the From field. My guess is that even if this worked, there'd be too many queries (too many people to group for) and each time you added/removed a query, the Union set would have to change - not practical at all. I also have tried to think of a subquery statement (even with self joins) but no joy. IF the records for any given person were grouped and guaranteed to be in chronological order, I think the only way you could do this is with code in a fashion similar to something I've done before. You should be able to arrange the data this way with a query.

Basically, you'd need pairs of From, To, ID and Location variables and would loop through a recordset. With record1, ensure From1 < To1 and set ID1 and Location1 variables. Move to rec2, and see if ID and Location is the same as 1. If yes, check dates again and set the To2 date variable to the new value, move next and repeat until ID or Location changes. As soon as either changes, you have your start and end dates for a location and ID. You'd then reset Location1 etc. to the values in the record where you found the change and start the loop over. I imagine it sounds very complicated if you've never done anything like it. One thing I found helpful for such an exercise is to map it out in a document or spreadsheet so you can plan the logical steps, then start writing the actual code. Maybe somebody will have a simpler idea for you!

[InsuranceGuy]

Please tell me it is possible!!!

I hope you all will forgive me, but "IT IS POSSIBLE".
I also believe it is fairly complicated and must be completed in VBA, not in a query.

You would need to iterate the recordset and compare your startDates to the previous record's endDates when the employee ID location is the same. When the date values abut, you would need to replace the original record's end date with the current record's endDate and dismiss the current record. After iterating the entire source recordset, you would need to output your procedure's findings to a new table for storage/use.

[Micron]

InsuranceGuy, if you need forgiveness, then so do I.

I think the only way you could do this is with code...and would loop through a recordset.