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...
Erik Lupien
Last Activity: Sep 3 '08, 05:41 PM
Joined: Jul 15 '08
Location:
-
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...Leave a comment:
-
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...Leave a comment:
-
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...Leave a comment:
-
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...Leave a comment:
-
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...Leave a comment:
-
I cannot post an attachment! :(
ErikLeave a comment:
-
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....Leave a comment:
-
Sorry, could you elaborate on this or provide an example to help visualize what you mean?
Thanks, Erik...Leave a comment:
-
Status update ...
I modified the 2-week intervals table and added a column "Interval" in which I number each interval in a sequence from 1 to 353.
I also changed the script so that the results table displays the 2-week interval's interval nmber (1 to 353).
I extracted from my leave database all sick leave entries for fiscal 2007 to 2008 for employees with more than 52.5 hours of sick leave....Leave a comment:
-
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...Leave a comment:
-
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.
...Leave a comment:
-
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,...Leave a comment:
-
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...Leave a comment:
-
Hi there,
LSTAR is the field that indicates the leave.
I hope this sorts things out properly.
ErikLeave a comment:
-
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...Leave a comment:
-
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.
- What is the Name of the Back End Database on the Server?
The MDB is called LEAVE 2008 - Are we talking about an Access Database or Project,
Leave a comment:
- What is the Name of the Back End Database on the Server?
-
Querying database for employees with X hours of sick leave
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...
No activity results to display
Leave a comment: