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]

Nice problem :-)
In a similar "find the period/gap" case I've used a + and - string to get the problem solved.

Approach:
1) Create an initial string for the whole period e.g. of 365 times the '-' character.
2) Process all records and update the day position of sick leave with a "+"
(No problem to set the same day twice or more times, just use the start and end date of each row)
3) Get the results by using INSTR(strYear," ++++++++++") and working your way through the string from left to right.

Getting the idea ?

Nic;o)

Hello Nico, I would also like to see a concrete example of your approach, since I am having trouble visualizing it also.

[NeoPa]

Subscribing - and hopefully clarifying the last point a little.

I believe the idea, within a specific time-frame (a year for instance), is to build up a string with a character (-) in each position to indicate a normal day.

Dim strYear As String
 
strYear = String(365, "-")

The position of the character within the string is the mapping of which day in the year it represents.

Processing through the records you have for an employee, you can replace each "-" with a "+" for each day they are absent.

Simplifying for a week (strWeek starts on the Sunday), if an employee had Wednesday and Thursday off for that week, the value in strWeek (line #2) would be :

=SMTWTFS
"---++--"

[ADezii]

Subscribing - and hopefully clarifying the last point a little.

I believe the idea, within a specific time-frame (a year for instance), is to build up a string with a character (-) in each position to indicate a normal day.

Dim strYear As String
 
strYear = String(365, "-")

The position of the character within the string is the mapping of which day in the year it represents.

Processing through the records you have for an employee, you can replace each "-" with a "+" for each day they are absent.

Simplifying for a week (strWeek starts on the Sunday), if an employee had Wednesday and Thursday off for that week, the value in strWeek (line #2) would be :

=SMTWTFS
"---++--"

Thanks for the clarification, NeoPa.

[ADezii]

Hello Eric, I do believe that we are getting close to a resolution to your problem. Download the Attachment, run it against the test data provided, and against your own data, then get back to me with feedback. I won't be able to spend much more time on this Project, but I would definately like to see it concluded.

[Erik Lupien]

Hi there,

I'm sorry it's taken a while to respond.

Believe it or not, my computer is still crushing the data. It's been going at it since 9:00 a.m. last Thursday morning (July 24). My system hasn't crashed and the task manager tells me MSACESS.exe is using anywhere from 55 to 65% of my computer's CPU resources (it changes every second or so between those percentages) - so I'm guessing it's still processing the data.

Having said all this, I haven't had the opportunity to try the new code yet!

Assuming I'm not wrong on Access still processing (if someone here has something to say about it please so), I will let it continue. And as soon as it's done, I will let you know.

As for the code, I added entries to the leave 2007-2008 table to show employee 99765426 sick for the entire month of February 2008 and ran the updated script on another computer ... here's what it generated:

Employee          Start Date           End Date           Sick Hours         Interval#
12345678 ===> 08-10-2007 ===> 08-23-2007 =====> 67.5 =======> 145
12345678 ===> 08-16-2007 ===> 08-29-2007 =====> 67.5 =======> 151
12345678 ===> 08-18-2007 ===> 08-31-2007 =====> 67.5 =======> 153
12345678 ===> 08-20-2007 ===> 09-02-2007 =====> 67.5 =======> 155
55555555 ===> 08-08-2007 ===> 08-21-2007 =====> 75.0 =======> 143
55555555 ===> 08-10-2007 ===> 08-23-2007 =====> 67.5 =======> 145
99765426 ===> 04-01-2007 ===> 04-14-2007 =====> 67.5 =======> 001
99765426 ===> 04-01-2007 ===> 04-14-2007 =====> 67.5 =======> 001
99765426 ===> 05-08-2007 ===> 05-21-2007 =====> 60.0 =======> 038

As you can see, it didn't show employee 99765426 sick the entire month of February 2008 or at all during that month. Any idea why?

About the VBA code that could find a break in the 2-week intervals for an employee ... what do you think about the idea of creating two arrays, one with the numbers I have, and another one with the numbers expected, then looping through them and compare each value, if they aren't equal it's 'broken'. Something like this:

Dim i&,x&
Redim arExpected(353)
For i=0 to UBound(arExpected)
   x=x+1:arExpected(i)=x
Next

PS I will attach the updated file in the next post.

Erik

[Erik Lupien]

I cannot post an attachment! :(

Erik

[nico5038]

Hi Erik,

Just click on the Edit/Delete link of the comment after posting, it will allow the placement of an attachment.

I see I missed some comments from Neopa And ADezii about the +/- approach.

A small sample to show how it works for a two week period searching for 3 day's sick leave:
Input:

John 1/1/2008 1/5/2008
John 1/9/2008 1/11/2008

Processing by creating two weeks initially in strPeriod:

------------

After first row:

+++++---------

After second row:

+++++---+++---

Now using Instr(strPeriod ,"+++") will return 1 as being the first leave of three days.
To check for multiple leaves you can test:

IF InstrRev(strPeriod,"+++") = Instr(strPeriod,"+++")  then
   ' only one found as we find the same start position
else
  ' multiple processing necessary
endif

The multiple processing can be done using a switch between testing for "+++" and "-" (when a period in between leaves can be only one day, you could also use more by enlarging the string)
The test for the end of the period in the sample would be:

Instr(Instr(strPeriod,"+++"),strPeriod,"-")

This will return the value 6.
That can be used for the startpos in Instr(startpos, strPeriod,"+++" ) to find the next leave, etc..

Bit clearer ?

Nic;o)

[ADezii]

Hi Erik,

Just click on the Edit/Delete link of the comment after posting, it will allow the placement of an attachment.

I see I missed some comments from Neopa And ADezii about the +/- approach.

A small sample to show how it works for a two week period searching for 3 day's sick leave:
Input:

John 1/1/2008 1/5/2008
John 1/9/2008 1/11/2008

Processing by creating two weeks initially in strPeriod:

------------

After first row:

+++++---------

After second row:

+++++---+++---

Now using Instr(strPeriod ,"+++") will return 1 as being the first leave of three days.
To check for multiple leaves you can test:

IF InstrRev(strPeriod,"+++") = Instr(strPeriod,"+++")  then
   ' only one found as we find the same start position
else
  ' multiple processing necessary
endif

The multiple processing can be done using a switch between testing for "+++" and "-" (when a period in between leaves can be only one day, you could also use more by enlarging the string)
The test for the end of the period in the sample would be:

Instr(Instr(strPeriod,"+++"),strPeriod,"-")

This will return the value 6.
That can be used for the startpos in Instr(startpos, strPeriod,"+++" ) to find the next leave, etc..

Bit clearer ?

Nic;o)

Excellent Tutorial Nico, thank you.

[NeoPa]

I cannot post an attachment! :(

Erik

Let me know if this is resolved now. After posting, any post can be edited for about an hour. If you scroll down the page there is a "Manage Attachments" link. It should be fairly straightforward from there.

Also, I notice you're not using the [ CODE ] tags (the # button) in your posting generally. I can easily see that you're doing your best to be as little trouble as possible, so don't see this as criticism. Just a gentle reminder of how we like to see the posts laid out (it helps a lot to understand what's posted when it's properly laid out).

Welcome to Bytes :)

[ADezii]

Hello Erik:
Thanks to Nico's 'String Theory' (Posts #19 and 27), there may be some light at the end of the tunnel as far as getting a resolution to your problem. When I get the chance, I'll revamp the existing code and see what happens. Worst case scenario is that we should be able to generate a list of every employee who has single/multiple, non-contiguous Sick Leaves, along with a Total Count, for the Fiscal Year. Not sure when I can get to it, but keep in touch.