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]

Hello again, Erik, major oversight on my part. Since we changed the manner in which we determine how many Holidays occur within a specific 2 Week Interval, (multiple Boolean Fields vs. single Byte Field), the actual value is now contained in rstIntervals via rstIntervals![Holidays]. We can now extract this value directly from the Recordset as opposed to executing a DLookup() on tbl2WeekInterva ls with the criteria of [Start_Date] and [End_Date] within the Range. We should now experience a significant speed improvement, I'll run the tests and post them after I display the latest code Revisions below for Phase I:

Public Sub cmdTest_Click()
On Error GoTo Err_cmdTest_Click
Dim strSQL As String
Dim MyDB As DAO.Database
Dim rstUniqueEmployees As DAO.Recordset
Dim rstFinalResults As DAO.Recordset
Dim rstIntervals As DAO.Recordset
Dim bytHolidaysInInterval As Byte
Dim varTotalSickHours As Variant
Dim strInterval As String
Dim lngStartTime As Long
Dim lngEndTime As Long
 
DoCmd.Hourglass True    'visual indicator of processing
lngStartTime = timeGetTime()    'Start the Timer
 
'Generate Unique Employee IDs in Ascending Order
strSQL = "SELECT DISTINCT [LEAVE 2007-2008].PERNR FROM " & _
         "[LEAVE 2007-2008]ORDER BY [LEAVE 2007-2008].PERNR;"
 
'Delete all entries in the previous tblSickLeave Output Table
CurrentDb.Execute "Delete * From tblSickLeave", dbFailOnError
 
Set MyDB = CurrentDb()      'refers to the Current Database
Set rstUniqueEmployees = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
Set rstIntervals = MyDB.OpenRecordset("Select * From tbl2WeekIntervals;", dbOpenSnapshot)
Set rstFinalResults = MyDB.OpenRecordset("tblSickLeave", dbOpenDynaset)
 
'Cross reference every Employee ([PERNR]) against every 2 week Interval ([Start_Date]/[End_Date]),
'at this intersection check the value of the [Holiday] Field (0, 1, 2, or 3), to see
'if a Holiday/Holidays occurred for the specified Interval, calculate the Total Hours [CATSHOURS]
'for each Employee/Interval. Check and see if this figure is at least 75 Hours for a No-Holiday-Interval,
'or 67.5 Hours for a single Holiday Interval, 60 Hours for a 2-Holiday Interval, or 52.5 Hours for a
'a 3-Holiday Interval.
With rstUniqueEmployees     'Employees
  Do While Not .EOF
    Do While Not rstIntervals.EOF       '2-Week Intervals
      'bytHolidaysInInterval = DLookup("[Holidays]", "tbl2WeekIntervals", "[Start_Date] = #" & _
                                     'rstIntervals![Start_Date] & "# And [End_Date] = #" & _
                                     'rstIntervals![End_Date] & "#")     '0,1,2, or 3 Holiday(s) in Interval?
       bytHolidaysInInterval = rstIntervals![Holidays]
      'Total Sick Hours for the Employee for the specified 2-Week Interval
      varTotalSickHours = DSum("[CATSHOURS]", "LEAVE 2007-2008", "[WorkDate] Between #" & _
                                rstIntervals![Start_Date] & "# And #" & rstIntervals![End_Date] & _
                                "# And [PERNR] = " & ![PERNR])
        If bytHolidaysInInterval = 0 Then           'NO Holiday in Interval
          If varTotalSickHours >= 75 Then           'minimum of 75 hrs. for NO Holiday
            strInterval = strInterval & "+"         'qualifies
          Else
            strInterval = strInterval & "-"         'non-qualifier
          End If
        ElseIf bytHolidaysInInterval = 1 Then       '1 Holidays in Interval
          If varTotalSickHours >= 67.5 Then         'minimum of 67.5 hrs. for 1 Holiday
            strInterval = strInterval & "+"         'qualifies
          Else
            strInterval = strInterval & "-"         'non-qualifier
          End If
        ElseIf bytHolidaysInInterval = 2 Then       '2 Holidays in Interval
          If varTotalSickHours >= 60 Then           'minimum of 60 hrs. for 2 Holidays
            strInterval = strInterval & "+"         'qualifies
          Else
            strInterval = strInterval & "-"         'non-qualifier
          End If
        ElseIf bytHolidaysInInterval = 3 Then       '3 Holidays in Interval
          If varTotalSickHours >= 52.5 Then         'minimum of 52.5 hrs. for 3 Holidays
            strInterval = strInterval & "+"         'qualifies
          Else
            strInterval = strInterval & "-"         'non-qualifier
          End If
        End If
      rstIntervals.MoveNext     'Next Interval for Employee
    Loop
    rstFinalResults.AddNew
      rstFinalResults![Employee] = ![PERNR]             'Add Employee ID
      rstFinalResults![IntervalString] = strInterval    'Add concatenated Pattern String
    rstFinalResults.Update
 
    strInterval = ""            'Reset for next Employee
    rstIntervals.MoveFirst      'Move to the next 2-Week Interval
    .MoveNext                   'Move to the next Employee
  Loop
End With
 
rstIntervals.Close
rstUniqueEmployees.Close
rstFinalResults.Close
Set rstUniqueEmployees = Nothing
Set rstIntervals = Nothing
Set rstFinalResults = Nothing
 
Me![cmdFinalize].Enabled = True
 
lngEndTime = timeGetTime()      'End Timer (end of the line!)
 
Debug.Print "************ Process 1 Execution Time ************"
Debug.Print "Test PC: Pentium(R) M - 1.60 GHz - 512 MB RAM"
Debug.Print "Seconds: " & FormatNumber((lngEndTime - lngStartTime) / 1000, 2)
Debug.Print "Minutes: " & FormatNumber(((lngEndTime - lngStartTime) / 1000) / 60, 2)
Debug.Print "Hours  : " & FormatNumber((((lngEndTime - lngStartTime) / 1000) / 60) / 60, 4)
Debug.Print "**************************************************"
 
DoCmd.Hourglass False       'indicates end of processing
 
Exit_cmdTest_Click:
  Exit Sub
 
Err_cmdTest_Click:
  DoCmd.Hourglass False
  MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
  Resume Exit_cmdTest_Click
End Sub
 

Timing Results:

Process    Seconds    Minutes    Hours       Records    
   1    781.3     12.84        0.217     5000            
   1    672.82   11.22       0.1869       5000    

P.S. - Next step, I'll run the original and revised code against the 100,000+ Records and let you know how I made out.

[ADezii]

As one of my PCs is processing your 100,000+ Records and generating Sick Leave Data, I am looking at the overall picture, and the numbers are staggering. Here are my thoughts on the Topic:

1. The Data set you gave me consists of 100,017 Records in the Leave 2007-2008 Table.
2. Within these Records are 5,359 Employees with accumulated data.
3. There are 353 distinct 2-Week Intervals for the Fiscal Year 4/1/2007 - 4/14/2007 to 3/18/2008 to 3/31/2008.
4. Each and every Employees has to be cross-referenced against each and every 2-Week Interval. That's 1,891,727 iterations involving 2 Nested Loops, I know of no other way to accomplish this.
5. At each juncture of Employee <==> 2-Week Interval, the following must be done:
   1. The number of Holidays within that Interval has to be determined, that info is already contained within the Recordset, so no problem there.
   2. The Total Sick Hours for each Employee per Interval has to calculated.
   3. This summation has to be compared to certain constant values depending on the Holidays occurring in that Interval.
   4. The Pattern String has to be appended to with either a '-' or "*" depending on the previous outcome.
   5. This is really an enormous, and very time consuming, undertaking.
   6. If I remember correctly, the actual Record Count that this logic would have to be performed on is in the area of 650,000 Records - staggering.
   7. With all this in mind, I would recommend the following:
      • Seek professional help, not for you of course! It seems as though this functionality is critical to your operations, so have a professional programmer look at it. He/she may come up with a completely different, and more efficient, algorithm to accomplish the same task. I have done all that I can.
      • Segment the data as you have done, breaking the Data Set into 6 - 100,000+ segments, process each, then Append the data into a Master Table.
      • Get a super fast PC, preferably the HAL 2000 from 2001 a Space Odyssey (LOL).
      • Restrict the number of 2-Week Intervals acted on, do you really need data for all 353 Intervals?
6. Phase II, in my opinion is already optimized, and will efficiently analyze the Pattern Strings and generate the Total Sick Leave Data that you are requesting.
7. I'll post the Timing Figures for the processing of the 100,017 Records as well as for the Phase II operations on the results of Phase I processing.
8. For all practical purposes, once this has been accomplished, this Thread will effectively be closed.

[ADezii]

OK, here we go Erik! I've just completed my Benchmark Tests running the revised code on the 100,017 Records in the Leave 2007-2008 Table and here are the results:

************ Process 1 Execution Time ************
Records:   100,017
Test PC: Pentium(R) M - 1.60 GHz - 512 MB RAM
Seconds:   253,358.56
Minutes:     4,222.64
Hours  :        70.3774
Days:            2.93
**************************************************

If I remember correctly, when you ran the original code against the same Data Set, it took 3.5 days with a considerably faster PC. You can interpret these results as you wish, but there seems to be at least significant improvement. In my opinion, the code in Phase 2 is already optimized and performs quite well analyzing the 5,359 Pattern Strings and outputting the Aggregate Totals. In my mind, this is not even a consideration. All good things must come to an end, and I must say that this is the end of the road as far as this Thread is concerned. I have done as much as I can with my limited skills, I can do no more. I've Attached the Optimized code with no Records in tblEvent or tblEventTemp to make sure that you are completely up to date. Good luck with your Project and let me know how you are making out from time to time.

[Erik Lupien]

Dear ADezii,

How can I thank you enough?

I am totally grateful and appreciative of the excellent work you've done.

What you put together is unlike anything my employer has seen before and the results it generates will go a tremendously long way to helping managers in my organization better identify and help employees who are absent on sick leave come back to work sooner.

Thank you, thank you, and thank you on behalf of my employer, the hundreds of managers I work for, and the thousands of employees who stand to benefit from your excellent work.

If this also doesn't seem too inappropriate, I also want to thank the owners of this site for having made this possible, and for others who contributed ... the + and - string was a wonderful solution that works beautifullly.

Erik Lupien

[Stewart Ross]

Hi ADezii. Erik's kind words in his last post are tribute to what has been an extraordinary contribution on your part to this thread. Wonderful to read, even though going well beyond the norm in all sorts of ways...

Well done, and my goodness what a standard you set for the rest of us!

-Stewart

[ADezii]

Hi ADezii. Erik's kind words in his last post are tribute to what has been an extraordinary contribution on your part to this thread. Wonderful to read, even though going well beyond the norm in all sorts of ways...

Well done, and my goodness what a standard you set for the rest of us!

-Stewart

Thanks Stewart for your kind words.

[ADezii]

Dear ADezii,

How can I thank you enough?

I am totally grateful and appreciative of the excellent work you've done.

What you put together is unlike anything my employer has seen before and the results it generates will go a tremendously long way to helping managers in my organization better identify and help employees who are absent on sick leave come back to work sooner.

Thank you, thank you, and thank you on behalf of my employer, the hundreds of managers I work for, and the thousands of employees who stand to benefit from your excellent work.

If this also doesn't seem too inappropriate, I also want to thank the owners of this site for having made this possible, and for others who contributed ... the + and - string was a wonderful solution that works beautifullly.

Erik Lupien

Hello Erik, no thanks are necessary since that is why we are all here. It was a true pleasure working with you on a very challenging Project. I do have but one simple request, please keep in mind that it was Nico5038 that came up with the innovative Pattern String solution which enabled me to correctly analyze the Sick Leave Cycles. It if wasn't for Nico's code, I may have never arrived at a viable solution, or if it did, it would have been significantly delayed. We both owe a lot to him. Take care Erik.

[nico5038]

Don't give me too many credits ADezii, I've merely shared a concept and left the hard (coding) part to you :)

A job well done!

Nic;o)

[ADezii]

Two closing points, Erik, and two I feel that are worth mentioning:

1. When you are running this code, if applicable, turn OFF the following Sevices. For optimal efficiency, and the fastest possible processing time, you want to make sure that the CPU is dedicating a maximum percentage of its time to your code processing.
   • Hibernation
   • Suspend Mode
   • Windows Update
   • Anti Virus Software
   • Screen Saver
   • Disk Defragmentation or Optimization Programs
   • Any Resident Applications
2. Each time this code is executed, Records are dynamically Deleted and Appended to tblSickLeave. Given this cycle, the Database should periodically be Compacted and Repaired.