I am trying to solve a vb sequence number problem which has eluded me for several years now (yes really)
Consider the MS access table below:
Staff Start date End date Sequence
1111 01-Jan-15 04-Jan-15 1
2222 14-Jan-15 16-Jan-15 2
2222 17-Jan-15 22-Jan-15 2
3333 09-Jan-15 13-Jan-15 3
3333 15-Jan-15 23-Jan-15 4
4444 03-Jan-15 03-Jan-15 5
5555 11-Jan-15 11-Jan-15 6
5555 12-Jan-15 24-Jan-15 6
5555 26-Jan-15 28-Jan-15 7
6666 07-Jan-15 22-Jan-15 8
This contains the staff ID numbers and the start and end dates of employees sickness absence. The table is ordered by staff ID and then start date. I need to be able to create a sequence number against each employee based on the start and end dates of their sickness absence. The sequence number only increments under two circumstances firstly, if the employee number changes and secondly (this is the hard bit) if the end date of the previous period of sickness is NOT contiguous with the start date of the next one. Hopefully you should get the idea by looking at the example sequence numbers.
I would really appreciate any help with this as it has me completely stumped. Have I a snippet of code to show you? No I haven't as I have no idea where to start.
Kind regards
Alan