Combine records where there are consecutive months into one record, Access 2010

[pokan]

Hi, I have an employee segment table, there are situations where an employee will have multiple record segments. I like to combine the multiple records into one record if the date in the segments are consecutive months, either into a new table or in a query. Please see example.


For example: (Fields: employee_name | segment_start_date | segment_end_date)
J Smith | 11/1/15 | 12/31/15
J Smith | 1/1/16 | 4/30/16


J Smith | 5/1/16 | 6/30/16
J Smith | 11/1/16 | 12/31/16


The end result should be two records:
J Smith | 11/1/15 | 6/30/16 (because from 11/1/16 to 6/30/16, there was no break in J Smith's employment.)
J Smith | 11/1/16 | 12/31/16 (because after 6/30/16, there was a break, and then from 11/1/16 to 12/31/16, there was no break.)


I would appreciate any advice you guys can give. Thanks!

[ssanfu]

The only way I would know of to do this is using (a lot of) VBA and writing to a new table.

Not hard, but it will take a fair amount of time to write the code and debug the code and verify the results.