Querying database for employees with X hours of sick leave

[Erik Lupien]

Could someone, anyone, help me and compose a VBA script for me to use with MS Access?

I know nothing about MS Access of VBA scripting for it but really need some help.

I have an MS Access database on a server at work that records employee sick leave, by hour, by date.

I need to know the number of occurences when employees were gone on sick leave for 10 or more consecutive work days, during a calendar year that starts on Jan 1 and ends on Dec 31.

Employees work 7.5 hours in a day, Monday to Friday, no weekends.

I was thinking I need a script that runs a check for 75 hours of sick leave in each 14 day spread during the year.

If I counted right, there should be 255 such spreads: Starting with Jan 1 to 14, then Jan 2 to 15, ... and ending with Dec 18 to 31.

If an employee is gone for 30 consecutive work days, the result should only show him once. But if he goes 10 work days, comes back to work for 5 work days, then goes for another 10 work days, the result should show him twice.

I hope this makes sense and that someone can rise to the challenge and put this together.

Thank you

Erik Lupien

[ADezii]

Erik, I think this problem is a little more complicated than you realize, at least in my interpretation of it. If you are not in any great rush I'll be glad to have a look at it, but first some preliminary information.

1. What is the Name of the Back End Database on the Server?
2. Are we talking about an Access Database or Project, where the Back End Database resides on an SQL Server?
3. What is the Table Name containing the relevant Fields?
4. What are the Field Names, and their respective Data Types?
5. Am I correct in interpreting your request to mean you are looking for all occurrences of Sick Leave for 10 or more consecutive days? A single period of 20 or more days would still constitute a single occurrence?

[Erik Lupien]

Hi there, I'm not in a rush at all. I would greatly appreciate this help. And I make no illusion that this is complex. I started to think about it and got lost when I tried to figure it out.

The answers to your questions are below, I hope they are what you were looking for.

1. What is the Name of the Back End Database on the Server?
   The MDB is called LEAVE 2008
   
2. Are we talking about an Access Database or Project, where the Back End Database resides on an SQL Server?
   It is an Access database.
   
3. What is the Table Name containing the relevant Fields?
   Table name is LEAVE 2007-2008
   
4. What are the Field Names, and their respective Data Types?
   Field Name is L* (long integer number)
   The Values you want are 2100 (uncertfied sick leave) and 2200 (certified sick leave)
   The Hours column is called CATSHOURS (double number)
   The Date Field is called WORKDATE (date field)
   
5. Am I correct in interpreting your request to mean you are looking for all occurrences of Sick Leave for 10 or more consecutive days? A single period of 20 or more days would still constitute a single occurrence?
   Yes you are correct.
   

Thank you again,

Erik

[ADezii]

Hi there, I'm not in a rush at all. I would greatly appreciate this help. And I make no illusion that this is complex. I started to think about it and got lost when I tried to figure it out.

The answers to your questions are below, I hope they are what you were looking for.

1. What is the Name of the Back End Database on the Server?
   The MDB is called LEAVE 2008
   
2. Are we talking about an Access Database or Project, where the Back End Database resides on an SQL Server?
   It is an Access database.
   
3. What is the Table Name containing the relevant Fields?
   Table name is LEAVE 2007-2008
   
4. What are the Field Names, and their respective Data Types?
   Field Name is L* (long integer number)
   The Values you want are 2100 (uncertfied sick leave) and 2200 (certified sick leave)
   The Hours column is called CATSHOURS (double number)
   The Date Field is called WORKDATE (date field)
   
5. Am I correct in interpreting your request to mean you are looking for all occurrences of Sick Leave for 10 or more consecutive days? A single period of 20 or more days would still constitute a single occurrence?
   Yes you are correct.
   

Thank you again,

Erik

I will be very busy in the next few days, but I'll have a look at it as soon as I can. In the meantime, kindly post some data that would represent no Sick Leave, at least 10 days of Sick Leave, then a few days of no Sick Leave again. I need to visually see what constitutes Sick Leave usage and what determines when it is continuous. Kindly post the relevant Field Names along with the data. Will see you later.

[Erik Lupien]

Hi there,

Below is what I believe you asked for.

I think you may also want to note holidays, which pose an added challenge.

In the table below, the date 2007-09-03 (a Monday) was a holiday. It was not worked by the employee who was on paid holiday leave. There is no leave record for this activity on this table. But the employee was sick and away for a least 5 working days before that date and at least 5 working days after it (and therefore, at least 10 working days in and around that date).

Holidays in 2007 this far to note:
2008-01-01
2008-03-21
2008-03-24
2008-05-19
2008-01-01

SAMPLE OF THE TABLE, AFTER DATA IS PULLED FROM THE DATABASE

PERNR = Employee Number
WORKDATE = Date the employee was off
SKOSTL = Centre to which the cost is assigned
LSTAR = Field through which leave code is pulled from the database
AWART = Field through which leave code goes into the database
LAEDA = Date leave was input into system
STATUS = Indication of whether or not the leave was approved by the manager
CATSHOURS = Duration in hours of the leave
ALLDF = Was leave all day, not compulsary
LTXA1 = Description of entry, not compulsary
TYPE = Shortcut for leave codes, not consistently used

PERNR WORKDATE SKOSTL LSTAR AWART LAEDA STATUS CATSHOURS ALLDF LTXA1 TYPE
91697378 2007-08-08 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-09 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-10 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-13 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-14 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-15 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-16 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-17 126347101 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-20 242313501 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-21 242313501 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-22 242313501 2100 2100 2007-08-30 30 0 21
91697378 2007-08-23 242313501 2100 2100 2007-08-30 30 0 21
91697378 2007-08-24 242313501 2100 2100 2007-08-30 30 0 21
91697378 2007-08-27 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-28 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-29 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-30 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-31 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-09-04 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-05 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-06 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-07 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-10 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-11 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-12 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-13 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-14 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-17 242311300 2100 2100 2007-09-18 30 0 21
91697378 2007-09-18 242311300 2100 2100 2007-09-18 30 0 21


Good luck!

Erik

[ADezii]

Hi there,

Below is what I believe you asked for.

I think you may also want to note holidays, which pose an added challenge.

In the table below, the date 2007-09-03 (a Monday) was a holiday. It was not worked by the employee who was on paid holiday leave. There is no leave record for this activity on this table. But the employee was sick and away for a least 5 working days before that date and at least 5 working days after it (and therefore, at least 10 working days in and around that date).

Holidays in 2007 this far to note:
2008-01-01
2008-03-21
2008-03-24
2008-05-19
2008-01-01

SAMPLE OF THE TABLE, AFTER DATA IS PULLED FROM THE DATABASE

PERNR = Employee Number
WORKDATE = Date the employee was off
SKOSTL = Centre to which the cost is assigned
LSTAR = Field through which leave code is pulled from the database
AWART = Field through which leave code goes into the database
LAEDA = Date leave was input into system
STATUS = Indication of whether or not the leave was approved by the manager
CATSHOURS = Duration in hours of the leave
ALLDF = Was leave all day, not compulsary
LTXA1 = Description of entry, not compulsary
TYPE = Shortcut for leave codes, not consistently used

PERNR WORKDATE SKOSTL LSTAR AWART LAEDA STATUS CATSHOURS ALLDF LTXA1 TYPE
91697378 2007-08-08 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-09 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-10 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-13 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-14 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-15 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-16 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-17 126347101 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-20 242313501 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-21 242313501 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-22 242313501 2100 2100 2007-08-30 30 0 21
91697378 2007-08-23 242313501 2100 2100 2007-08-30 30 0 21
91697378 2007-08-24 242313501 2100 2100 2007-08-30 30 0 21
91697378 2007-08-27 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-28 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-29 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-30 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-31 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-09-04 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-05 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-06 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-07 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-10 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-11 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-12 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-13 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-14 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-17 242311300 2100 2100 2007-09-18 30 0 21
91697378 2007-09-18 242311300 2100 2100 2007-09-18 30 0 21


Good luck!

Erik

Erik, one more question - what specifically indicates a Sick Leave Day?

1. [ALLDF] = X
2. [LTXA1] = Sick Leave
3. [ALLDF] = X AND [LTXA1] = Sick Leave
4. Something different - please specify

[Erik Lupien]

Hi there,

LSTAR is the field that indicates the leave.

I hope this sorts things out properly.

Erik

[ADezii]

Hi there,

LSTAR is the field that indicates the leave.

I hope this sorts things out properly.

Erik

Sorry Erik, but now you really have me confused. In Post #5 you stated that:

LSTAR = Field through which leave code is pulled from the database

1. How would this Field indicate Sick Leave if it has a constant value of 2100?
2. How would continuous Sick Leave for a period of at least 10 days be calculated if this value doesn't change?
3. Maybe I'm confused, but this must be explained before we can go any further.

[Erik Lupien]

Hi there,

LSTAR has many other codes, such as codes for bereavement leave, vacation leave, family-related leave, etc. The two sick leave codes for LSTAR are 2100 (which refers to uncertified sick leave, which means we didn't ask for a doctor's note) and 2200 (which refers to certified sick leave, which means we asked for a doctor's note).

Each row in the table I presented to you refers to a single day. The only way to calculate sick leave for 10 continous days is to count if there are 75 hours of sick leave (10 days x 7.5 hours per day).

A script would have to have a start date, e.g. Jan 1, and an end date, e.g. Jan 14 (making sure the 2 dates are 14 days apart), then check if there are 75 hours of sick leave within those date.

If there is a vacation day in those 14 days, the script would have to check for 67.5 hours of sick leave (9 days x 7.5 hours per day) plus 7.5 hours of vacation time (which is code 1120 - sorry I had not told you this before as I just realized it would be helpful in this way).

The script would then have to do all this again for the 2 week range starting on Jan 2 and ending on Jan 15, and continue to Jan 3 ending Jan 16, and so on until Dec 18 to Dec 31.

I think there are 255 10-day cycles that include weekdays only in a year (this means weekends are not included in the 10-day count). So I think there should be script would probably loop 255 times.

It becomes interesting when the script has to factor in the following possibility, given the way we record the sick leave. If someone is gone for 20 days, say from Jan 1 to Jan 20. That person would appear in the result for Jan 1 to 14, jan 2 to 15, jan 3 to 16 ... and so on until jan 7 to 20; so a total of 7 times, but we would only want to see a result of 1 time for this person.

I hope this helps. I may be off on the way to script this. I only my logic above as an example to demonstrate what I need as an end result.

Thank you again and cheers,

Erik

[ADezii]

Hi there,

LSTAR has many other codes, such as codes for bereavement leave, vacation leave, family-related leave, etc. The two sick leave codes for LSTAR are 2100 (which refers to uncertified sick leave, which means we didn't ask for a doctor's note) and 2200 (which refers to certified sick leave, which means we asked for a doctor's note).

Each row in the table I presented to you refers to a single day. The only way to calculate sick leave for 10 continous days is to count if there are 75 hours of sick leave (10 days x 7.5 hours per day).

A script would have to have a start date, e.g. Jan 1, and an end date, e.g. Jan 14 (making sure the 2 dates are 14 days apart), then check if there are 75 hours of sick leave within those date.

If there is a vacation day in those 14 days, the script would have to check for 67.5 hours of sick leave (9 days x 7.5 hours per day) plus 7.5 hours of vacation time (which is code 1120 - sorry I had not told you this before as I just realized it would be helpful in this way).

The script would then have to do all this again for the 2 week range starting on Jan 2 and ending on Jan 15, and continue to Jan 3 ending Jan 16, and so on until Dec 18 to Dec 31.

I think there are 255 10-day cycles that include weekdays only in a year (this means weekends are not included in the 10-day count). So I think there should be script would probably loop 255 times.

It becomes interesting when the script has to factor in the following possibility, given the way we record the sick leave. If someone is gone for 20 days, say from Jan 1 to Jan 20. That person would appear in the result for Jan 1 to 14, jan 2 to 15, jan 3 to 16 ... and so on until jan 7 to 20; so a total of 7 times, but we would only want to see a result of 1 time for this person.

I hope this helps. I may be off on the way to script this. I only my logic above as an example to demonstrate what I need as an end result.

Thank you again and cheers,

Erik

I did some preliminary work on your Project, but it may be too extensive to post. Download the Attachment, review the code and logic thoroughly, then get back to me whenever you can.

[Erik Lupien]

Hi again,

Wow, this is impressive - thank you so very much.

I've noted something that needs to be fixed, otherwise my results are inaccurate. The 2-week intervals around Easter weekend and the Christmas Holidays have 2 holidays.

Looking at the holiday table you created, I thought the solution could be to add another column titled "Holidays" and to have boxes to check for each 2 week interval, as is the case for the Holiday column?

I could then either check boxes in the Holiday or the Holidays column, not both, to reflect that the 2-week interval has either 1 holiday or 2 holidays. I would hope that the script could then check for 75 hours of consecutive sick leave in the 2 week intervals (when no boxes are checked in either column), or 67.5 hours (when boxes are checked in the Holiday column) or 60 hours (when boxes are checked in the Holidays column).

Lastly, could the holidays interval table start April 1, 2007 and end March 31, 2008, to reflect the fiscal year? And when going from 2007 to 2008 I think there are a few 2-week intervals required, e.g. Dec 19 to Jan 2, Dec 20, to Jan 3, etc. all the way to Dec 31, to Jan 13.

I looked at the VBA code, thinking maybe I can do this but it is way more sophisticated than what I remotely aware of working with so I'm really at your mercy here!

I hope you don't mind tweaking this.

Thank you again.

[Erik Lupien]

I just noticed another thing, having played with the holiday table and generating the sick leave data.

In the example below, the one employee is gone from August 8 to September 16. Is it possible for the VBA script to figure out that this is the same employee gone for one absence and only report it once, rather that report it 13 times as is the case here?

Thanks again and I look forward to your response.

Erik



Employee Start Date End Date Holiday in Interval? Total Sick Hours
91697378 2007-08-08 2007-08-21 0 75
91697378 2007-08-09 2007-08-22 0 75
91697378 2007-08-24 2007-09-06 -1 67.5
91697378 2007-08-25 2007-09-07 -1 67.5
91697378 2007-08-26 2007-09-08 -1 67.5
91697378 2007-08-27 2007-09-09 -1 67.5
91697378 2007-08-28 2007-09-10 -1 67.5
91697378 2007-08-29 2007-09-11 -1 67.5
91697378 2007-08-30 2007-09-12 -1 67.5
91697378 2007-08-31 2007-09-13 -1 67.5
91697378 2007-09-01 2007-09-14 -1 67.5
91697378 2007-09-02 2007-09-15 -1 67.5
91697378 2007-09-03 2007-09-16 -1 67.5

[ADezii]

Hi again,

Wow, this is impressive - thank you so very much.

I've noted something that needs to be fixed, otherwise my results are inaccurate. The 2-week intervals around Easter weekend and the Christmas Holidays have 2 holidays.

Looking at the holiday table you created, I thought the solution could be to add another column titled "Holidays" and to have boxes to check for each 2 week interval, as is the case for the Holiday column?

I could then either check boxes in the Holiday or the Holidays column, not both, to reflect that the 2-week interval has either 1 holiday or 2 holidays. I would hope that the script could then check for 75 hours of consecutive sick leave in the 2 week intervals (when no boxes are checked in either column), or 67.5 hours (when boxes are checked in the Holiday column) or 60 hours (when boxes are checked in the Holidays column).

Lastly, could the holidays interval table start April 1, 2007 and end March 31, 2008, to reflect the fiscal year? And when going from 2007 to 2008 I think there are a few 2-week intervals required, e.g. Dec 19 to Jan 2, Dec 20, to Jan 3, etc. all the way to Dec 31, to Jan 13.

I looked at the VBA code, thinking maybe I can do this but it is way more sophisticated than what I remotely aware of working with so I'm really at your mercy here!

I hope you don't mind tweaking this.

Thank you again.

Hello Erik, download the Attachment and see that several items were taken care of.

In the example below, the one employee is gone from August 8 to September 16. Is it possible for the VBA script to figure out that this is the same employee gone for one absence and only report it once, rather that report it 13 times as is the case here?

Therein lies a problem, when I get a chance I'll look into it more deeply. When you fill all your Holidays in, post them so I am working with the same Interval Table. If possible, it would also be easier to work with the actual data that you are referring to.

[Stewart Ross]

Hi Erik. ADezii is a very obliging person who creates extraordinary code - as you will have found - but I am getting a little concerned that this thread is taking voluntary assistance to lengths greater than would be expected in the circumstances. Our primary aim is to help you to help yourself, by providing part-solutions to problems which you can then adapt for your own needs. We cannot act as joint developers for you.

If ADezii is willing to continue assisting that is fine, but please bear in mind that the forum is not intended to be a bespoke development service...

Thank you

-Stewart

[Erik Lupien]

Hi Stewart,

Thank you for the reminder.

And thank you also to ADezii.

I have taken your reminder and what ADezii provided me and built on it myself.

For example, there are, at it turns out up to 3 holidays in a 2 week interval if looking at the Dec 24 to Jan 7 2-week interval. I've managed to amend the files myself.

What I'm struggling with at this point is how to eliminate from the results table entries for the same employee that has been gone for 15 days but would show up 2 times (once for each of the 2 14-day interval in those 15 days - day 1 to 14 and day 2 to 15).

I'm going to give this some thought and try my hand and hopefully get it working, otherwise I hope I might get some guidance/direction once more.

Erik

PS - how do I attach my new file if and when I need to?