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
48  5596  ADezii 8,834
Recognized Expert Expert
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. - What is the Name of the Back End Database on the Server?
- Are we talking about an Access Database or Project, where the Back End Database resides on an SQL Server?
- What is the Table Name containing the relevant Fields?
- What are the Field Names, and their respective Data Types?
- 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?
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, where the Back End Database resides on an SQL Server?
It is an Access database.
- What is the Table Name containing the relevant Fields?
Table name is LEAVE 2007-2008
- 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)
- 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 8,834
Recognized Expert Expert
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, where the Back End Database resides on an SQL Server?
It is an Access database.
- What is the Table Name containing the relevant Fields?
Table name is LEAVE 2007-2008
- 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)
- 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.
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 8,834
Recognized Expert Expert
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? - [ALLDF] = X
- [LTXA1] = Sick Leave
- [ALLDF] = X AND [LTXA1] = Sick Leave
- Something different - please specify
Hi there,
LSTAR is the field that indicates the leave.
I hope this sorts things out properly.
Erik
ADezii 8,834
Recognized Expert Expert
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
- How would this Field indicate Sick Leave if it has a constant value of 2100?
- How would continuous Sick Leave for a period of at least 10 days be calculated if this value doesn't change?
- Maybe I'm confused, but this must be explained before we can go any further.
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 8,834
Recognized Expert Expert
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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Eddie |
last post by:
Hi to all
I am building a database to track project expenses information. My
database has 4 main tables, Employees, Activity, project info and
rates.
We add a project and track activities on the project. Each employee
will add an activity on a certain date. What I need to do is check the
date the activity was added and then check what the...
|
by: uluvale96799 |
last post by:
Hi,
I'm very new to programming so forgive me for asking the dumb question.
I'm trying to develop a database application using c#, and I'm using
the northwind database. I'm currently using visual c# express edition
2005 as well as sql 2005 express.
Here's the thing I've been trying to figure out. I want to insert a new
row in the...
|
by: tc |
last post by:
Hi. I have a customer who is running our .net application, multiple clients
connecting to an Access database. This ONE customer reports a great speed
degredation when more than one client is connected.
First client logs on and the system runs correctly.
Second client logs on, both connected clients now run very slowly when
retrieving...
|
by: dharnE |
last post by:
I'm Hoping that this Problem will answer.The Problem is"Create A Program to compute the pay of employees as entered by the user using function statement" Sample Output:
Enter Number of Employees:3
Hours:50
Rate:5.25
Pays:262.50
------------------------------
Hours:6
Rate:5.00
Pays:30.00
------------------------------
|
by: lucky33 |
last post by:
My employer has asked me to create a database that will keep track of
the employee attendance. Time off / Time earned, excused / unexcused,
etc.
At my company from the 6th month of employment to the second year you
earn 8 hours of PTO each month, from the 2nd to 4th year you earn 10
hours PTO each month.
My question is how would I...
| | |
by: jjkeeper |
last post by:
Hi, I need to create an annual leave Database for the company. I admit I was a complete novice at this. But I got thrown the job and I have to do it. So here's what I get so far, but I got pretty much stuck for some days figuring out what to do. Sorry I can't think of a better title.
I have a tblMainProfile table, which stores everything about...
|
by: AllBeagle |
last post by:
Hello Everyone,
I'm trying to build a web-based application for a client of mine and I keep finding holes in my design, so I could use some guidance if anyone has any to offer. Let me try to explain what it is I'm trying to do and hopefully someone has an idea that's not going to take me a long time to implement and isn't above my...
|
by: =?Utf-8?B?TWlrZSBDb2xsaW5z?= |
last post by:
I want to allow users to search last names, using wildcards.
When I try to following in my web app:
//employees data was previously retrieved and stored in ViewState.
var filteredData = from p in employees.ToArray() select p;
filteredData = from c in employees
where SqlMethods.Like(c.Username, "%" + txtSearchCriteria.Text +
"%")
select...
|
by: lizBeth |
last post by:
Hi all,
i seem to have gotten stuck on this coursework, i am unsure as to how to implement a method in the main class to calculate the sum of all employees monthly salaries.
Everything else works on the program it does output the employees details and their monthly salaries. I just need to add them up and output a "Total Payroll:...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
