Create sequence number using employee ID and start end dates

[AlanCake]

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

[twinnyfo]

Could you explain the usage of the Sequence number? I understand how you want it to increment, just trying to understand why.

Additionally, when will this sequence number be generated? It could be possible to not have it in the Table at all (as it can become a bit of redundant data. However, I am sure there are ways of doing this. It just depends on how and when the Sequence Number is created.

[AlanCake]

Hi

Many thanks for the reply the sequence number is used to group absence spells together. If you have a look at my example sequence number 2 starts 14-Jan-15 and ends 22-Jan-15 I am interested in the length [the duration] of the absence (I group on the seq and use a Min Max function to get the total duration) Often the periods of absence go in as two week blocks (for reasons of sick lines etc).

It would be really useful if, say, once the table has been ordered I could then generate and update the table to include the sequence number. I have multiple queries setup to run against the data.

To be honest I can produce the sequence number at the moment however unfortunately I have to do that in Excel before importing the data into access.

Hope this makes sense

Alan

[jforbes]

Are days off, like weekends, included as records in the Table? Are there voids in dates that need to be tested against another table with valid work days or with a function to determine the difference between a weekday or a weekend?

[twinnyfo]

If you want to maintain the sequence number in the table, then I would recommend that the sequence number be generated every time you enter a new record (I assume this would/should be on a form).

Once the Staff member, Start and Stop Dates are entered, the code would verify that the data you entered is acceptable (no nulls), then search for the Staff member, evaluating whether the start date is between any of the previously entered absence dates. Do the same for the Just-entered stop date. If there is no match, then increment the max sequence number by one. If there is a match, then find the current sequence number.

I can't provide the code for you, but this should point you in the right direction. I'll be glad to work through any troubleshooting you may come across.

[AlanCake]

Hi

No weekends etc do not matter simply if by employee number the end date backs on the the start date 15th to 16th, 1st to 2nd etc.

I should have mentioned that essentially I will not be adding in data to the table. The data comes out of our HR system and then is sequenced. When the reports get run next time we again just take the data out of our HR system and sequence it.

Thanks

Alan

[twinnyfo]

Alan,

So the HR system spits out the data without sequence numbers and then you must update the sequences after the fact?

If that is the case, Then I would create a function that you could call that updates the records (based on the same principles described above).

Could be easily done with a recordset cycling through all records without a sequence number.....