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

[Erik Lupien]

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.

Thank you ADezii and NeoPa.

I've had a slight setback ... my employer has had to install a security update on my desktop ... which means whatever Access processed during the last week has been lost and I have to restart (grrrr).

Having said this, is it normal ADezii that the script you wrote would take 8 days to cycle through 317,000 entries? I have a 3.3 GHz processor with 2 GB of RAM ....

Erik

[ADezii]

Thank you ADezii and NeoPa.

I've had a slight setback ... my employer has had to install a security update on my desktop ... which means whatever Access processed during the last week has been lost and I have to restart (grrrr).

Having said this, is it normal ADezii that the script you wrote would take 8 days to cycle through 317,000 entries? I have a 3.3 GHz processor with 2 GB of RAM ....

Erik

Even with all the intensive processing, that is a little ridiculous. Nico5038 helped me out on this one with some new logic and code. Download the Attachment, run it against a subset of the data, not all of it, and see if the results are valid. I made many changes, let's see how well they work. Don't forget that in Phase 1 you are cross checking every single Employee against 353 2-Week Intervals, doing Lookups on the Interval Holidays, calculating Total Sick Hours per Employee per Interval, doing a comparative analysis against what the values should be, building a Pattern String, Appending Records to a Table...I think you get the idea. For right now, please check the code and see if it gives valid results on a 'small' section of data. See you soon.

[Erik Lupien]

Even with all the intensive processing, that is a little ridiculous. Nico5038 helped me out on this one with some new logic and code. Download the Attachment, run it against a subset of the data, not all of it, and see if the results are valid. I made many changes, let's see how well they work. Don't forget that in Phase 1 you are cross checking every single Employee against 353 2-Week Intervals, doing Lookups on the Interval Holidays, calculating Total Sick Hours per Employee per Interval, doing a comparative analysis against what the values should be, building a Pattern String, Appending Records to a Table...I think you get the idea. For right now, please check the code and see if it gives valid results on a 'small' section of data. See you soon.

Good morning,

This is becoming quite impressive, thank you again.

I modified the 2-week interval table to include a 3-holiday column (for the period during Christmas and New Years when there are 3 holidays - Dec 24, 25, and Jan 1). I also modified the VBA code to check for 52.5 hours during a 2 week interval with 3 holidays.

I also imported the first 5,000 entries of the 317,000 entries from the sick leave table I had (and replaced the entries in the Leave 2007-2008 table with these 5,000 entries.

I then ran "Generate Sick Leave" and then "Finalize Sick Leave Data".

I like the way the tblSickLeave table shows the consecutive 2-week intervals when an employee was on sick leave.

I also love the way the qrySickLeave table reports the unique sick leave entries.

As far as I can see, this now works beautifully.

I am now shopping for a more powerful computer in our organization where I can run this process without interruption. I will let you know how it works.

[ADezii]

Good morning,

This is becoming quite impressive, thank you again.

I modified the 2-week interval table to include a 3-holiday column (for the period during Christmas and New Years when there are 3 holidays - Dec 24, 25, and Jan 1). I also modified the VBA code to check for 52.5 hours during a 2 week interval with 3 holidays.

I also imported the first 5,000 entries of the 317,000 entries from the sick leave table I had (and replaced the entries in the Leave 2007-2008 table with these 5,000 entries.

I then ran "Generate Sick Leave" and then "Finalize Sick Leave Data".

I like the way the tblSickLeave table shows the consecutive 2-week intervals when an employee was on sick leave.

I also love the way the qrySickLeave table reports the unique sick leave entries.

As far as I can see, this now works beautifully.

I am now shopping for a more powerful computer in our organization where I can run this process without interruption. I will let you know how it works.

Adding the 3rd [Holiday In Interval] Column will slow the code execution down even further. You now should consider removing the [Holidays] Columns and replace them with a single Column named [Holidays] - {BYTE}. This Field would be Required, with valid entries of 0, 1, 2, or 3. Now, only a single Lookup through the tbl2WeekInterva ls Table would be needed, as opposed to 3. I would be willing to bet that code execution would be dramatically increased. Down Side, code readjustments.

[Erik Lupien]

Adding the 3rd [Holiday In Interval] Column will slow the code execution down even further. You now should consider removing the [Holidays] Columns and replace them with a single Column named [Holidays] - {BYTE}. This Field would be Required, with valid entries of 0, 1, 2, or 3. Now, only a single Lookup through the tbl2WeekInterva ls Table would be needed, as opposed to 3. I would be willing to bet that code execution would be dramatically increased. Down Side, code readjustments.

Thank you for the advice. I made the change to the 2-week intervals table and have 1 column for holidays with entries of 0, 1, 2, and 3. I then changed the code and have only 1 lookup of that column.

I ran tests for 5,000 entries (5 minutes), 10,000 (30), and 20,000 (2 hours), and all works beautifully. WOO HOO!

Now crunching through the 317,000 entries ... this will take a little time.

Erik

[ADezii]

Thank you for the advice. I made the change to the 2-week intervals table and have 1 column for holidays with entries of 0, 1, 2, and 3. I then changed the code and have only 1 lookup of that column.

I ran tests for 5,000 entries (5 minutes), 10,000 (30), and 20,000 (2 hours), and all works beautifully. WOO HOO!

Now crunching through the 317,000 entries ... this will take a little time.

Erik

I am curious to see what the processing time... is now. Anything has to be better than 8 days. You may also wish to consider segmenting the data, basically break it up into 3 - 100,000+ Record Blocks, run the same code on 3 different PCs on each Segment, then Append the 3 tblSickLeaves into a tblMASTERSickLe ave. Now, run the Query on tblMASTERSickLe ave to generate the final results. Processing time would but essentially cut by a factor of 1/3.

P.S. - I'm sure that you already know this but make sure if you take this 3 PC approach that the data in the individual tblSickLeaves represents unique Employees and that there is no overlap or duplication. If this were the case, the results would be misleading, to say the least. With all the Deleting and Appending going on, it would be a very good idea to periodically Compact and Repair the Database. My guess is that if you didn't, it would grow very large, very soon. I'll also give a final look at the code and see if it can be further optimized. Don't know if you noticed, but several Indexes were created on various Fields in order to increse the overall processing time.

[Erik Lupien]

I am curious to see what the processing time... is now. Anything has to be better than 8 days. You may also wish to consider segmenting the data, basically break it up into 3 - 100,000+ Record Blocks, run the same code on 3 different PCs on each Segment, then Append the 3 tblSickLeaves into a tblMASTERSickLe ave. Now, run the Query on tblMASTERSickLe ave to generate the final results. Processing time would but essentially cut by a factor of 1/3.

P.S. - I'm sure that you already know this but make sure if you take this 3 PC approach that the data in the individual tblSickLeaves represents unique Employees and that there is no overlap or duplication. If this were the case, the results would be misleading, to say the least. With all the Deleting and Appending going on, it would be a very good idea to periodically Compact and Repair the Database. My guess is that if you didn't, it would grow very large, very soon. I'll also give a final look at the code and see if it can be further optimized. Don't know if you noticed, but several Indexes were created on various Fields in order to increse the overall processing time.

Thank you again for the information.

I sorted the 317,000 entries by employee #, then divided the data into 3 chucks of about 105,000 entries each - I used the change in employee # as the break-point between one chunk and the next.

I began running 3 separate processes on 3 separate desktops at 3 p.m., Thursday, Aug 8. Hopefully they will be done today or, if not, by next Monday! :)

Erik

[Erik Lupien]

Update:

I successfully processed all the data, in 3 separate files on 3 separate desktops. Each took about 3.5 days to run about 100,000 entries.

My IT guy informed me early this week that he had made an error and only sent me the data for employees who took 75 hours of sick leave or more (not 52.5 hours or more - which would account for a 2-week intervals that have 1, 2, or 3 vacation days).

I'm now looking at more than 650,000 entries with the new, complete set of data.

I have a question about possibly speeding things up.

Since I can sort the Leave 2007-200 Table by employee number, is it possible to re-jig the code a little so that it cycles through the whole Table just once? For example, if entries 1 to 10 are for 1 employee, and 11 to 30 for a 2nd employee, etc., the code would assess the total leave hours for the 353 2-week intervals for those first 10 entries only, then it would assess the total leave hours for the 353 2-week intervals for entries 11 to 30 only, and so on?

This can't be happening now as it looks like the code cycles through the entire set of entries, which is why it takes long no?

Erik
by employee number at a time to figure out

[ADezii]

Update:

I successfully processed all the data, in 3 separate files on 3 separate desktops. Each took about 3.5 days to run about 100,000 entries.

My IT guy informed me early this week that he had made an error and only sent me the data for employees who took 75 hours of sick leave or more (not 52.5 hours or more - which would account for a 2-week intervals that have 1, 2, or 3 vacation days).

I'm now looking at more than 650,000 entries with the new, complete set of data.

I have a question about possibly speeding things up.

Since I can sort the Leave 2007-200 Table by employee number, is it possible to re-jig the code a little so that it cycles through the whole Table just once? For example, if entries 1 to 10 are for 1 employee, and 11 to 30 for a 2nd employee, etc., the code would assess the total leave hours for the 353 2-week intervals for those first 10 entries only, then it would assess the total leave hours for the 353 2-week intervals for entries 11 to 30 only, and so on?

This can't be happening now as it looks like the code cycles through the entire set of entries, which is why it takes long no?

Erik
by employee number at a time to figure out

Erik, first of all my code is out-of-sync with yours. Could you possibly E-Mail me your Database with the current code you are using along with approximately 25,000 Records. I know it is a tall order, but at this point we are looking at 2 different things, and I really need to test the code against a large data set. If this is acceptable, I think you have my E-Mail Address, if not let me know and I'll send to you in a PM. I can assure you of complete confidentiality as far as the data is concerned.

[ADezii]

Update:

I successfully processed all the data, in 3 separate files on 3 separate desktops. Each took about 3.5 days to run about 100,000 entries.

My IT guy informed me early this week that he had made an error and only sent me the data for employees who took 75 hours of sick leave or more (not 52.5 hours or more - which would account for a 2-week intervals that have 1, 2, or 3 vacation days).

I'm now looking at more than 650,000 entries with the new, complete set of data.

I have a question about possibly speeding things up.

Since I can sort the Leave 2007-200 Table by employee number, is it possible to re-jig the code a little so that it cycles through the whole Table just once? For example, if entries 1 to 10 are for 1 employee, and 11 to 30 for a 2nd employee, etc., the code would assess the total leave hours for the 353 2-week intervals for those first 10 entries only, then it would assess the total leave hours for the 353 2-week intervals for entries 11 to 30 only, and so on?

This can't be happening now as it looks like the code cycles through the entire set of entries, which is why it takes long no?

Erik
by employee number at a time to figure out

Hello Erik, I've made the following changes to the Database, and we'll proceed from there:

1. For testing purposes, I'm only using the first 5,000 Records of 1000,000+. This is primarily for time constraints during the initial testing phase. These 5,000 Records will be my new baseline for testing code changes against.
2. Added an accurate timing mechanism enabling me to perform Benchmark testing against the Baseline. Refer to Code Lines 11, 12, 15, 91, and 93 thru 98.
3. Changed the Variable blnHolidaysInIn terval from Variant to Byte and renamed it more appropriately to bytHolidaysinIn terval. This Variable will only contain values of 0, 1, 2, or 3 and there is no reason to Declare it as a Variant.
4. Set a Validation Rule on the [Holidays] Field in tbl2WeekInterva ls to Between 0 and 3. It is imperative that entries do not deviate from this Range. It is already Required and Indexed.
5. Changed intTotalSickHou rs from Variant to Integer for improved efficiency. The only way this should be declared as a Variant is if the possibility of Nulls exist in this Field. Does this possibility exist?
6. Changed the 4 If..End If constructs involving the analysis of the bytHolidaysInIn terval Variable to a single If...ElseIf...E nd If construct. Refer to Code Lines 44 thru 68. This, at least in my mind, should be more efficient. I'll run the timing test against the baseline with these changes and let you know.
7. Be advised that these changes affect Phase I only, I don't think that there is much we can do with Phase II, but that is another matter.
8. I would hope that you woul be right beside me on this, so kindly make all these changes in your code.
9. See you around after the testing of these changes. I'll post the results here.
10. P.S. - All testing against the Baseline will involve the Average of 3 Trial Runs.

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 intTotalSickHours As Integer
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] and [Holiday2] Fields, (True/False), 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, or 60 Hours for a 2-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?
      'Total Sick Hours for the Employee for the specified 2-Week Interval
      intTotalSickHours = 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 intTotalSickHours >= 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 intTotalSickHours >= 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 intTotalSickHours >= 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 intTotalSickHours >= 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