Is anyone up for a Challenge? Oh I HOPE SO!!!!

[TheNovice]

To the Best of the Best (yes I am sucking-up early),

Ok here it is:

I am trying to figure out a method that does the following. I have an
employee who works 5 days a week M-F. If they call in sick on Monday or
Friday he is assessed a point and a half (1.5), all other days are one (1)
point.

But, if he misses two or more consecutive days it is only 2 points MAX.

The catch is not including Saturday or Sunday

Now if he misses 3 straight weeks, it is still 2 points.

Just to add to the mix, if he comes in in the second week for just one day
and misses the rest of the time they are both 2 points.

We go with a rolling 6 month period (7/29 - 1/29 etc) so the points drop
after the the six month period.

My table(s) has the follow info.

tblPointData
-----------------------------
pdEmpID 'Employee ID
pdClkIn 'Date Clocked-In or Entry Date (if Absent)
pdType 'REG=Regular Day Worked, NOPAY=usually a personal day, NOP S=No Pay
Sick(NOPAY and NOP S are both absences unless a Supervisor Excuses the Missed
Day
pdPoints 'Points assessed

tblEmpSchd
-----------------------------
esEmpID 'Employee ID
esWrkSchdCode 'Work Schedule Code
esWrkU 'Yes/No Days Worked
esWrkM 'Yes/No Days Worked
esWrkT 'Yes/No Days Worked
esWrkW 'Yes/No Days Worked
esWrkR 'Yes/No Days Worked
esWrkF 'Yes/No Days Worked
esWrkS 'Yes/No Days Worked

Can anyone give me a simple method to Code this so that my table
(tblPointdata)will be updated with the points that need to be assessed to
each employee.

 

[KARL DEWEY]

You need a field to record ‘Supervisor Excuses the Missed Day’
tblEmpSchd is only for one week. How do you track the week before?

 

[TheNovice]

I am not looking per week, this is a Rolling 6 months.

I do have the Info for the Excused Days, and those are filtered out through
a query.

Any Ideas?

 

[KARL DEWEY]

I am not looking per week, this is a Rolling 6 months.
Does this mean that everyone is on the same shift schedule for six months?
a query.
That information is necessary to calculate points correctly. Based on your
table presented points can not be calculated.

 

[TheNovice]

I have since made chages to to Db. The data that is extracted from our time
clock systems has the Shift at the time the driver clocks in or out. so the
information is always there no matter the change in schedules. Schedule
changes are done to begin the following week (or later) so there is no
conflict in schedules.

the tblEmpSched only has the esWrkSchdCode and Days and the tblPointdata has
always had WrkSchdCode field.

I just need a sample code that can get me started and I can test and modify
on the fly if necessary.

 

[Jeff Boyce]

You may want "the sample code ...", but Karl is pointing out that the data
needed to do the calculation was not available. Can you write out the
formula for the calculation you want, using the fields you currently have?
If so, post it back here. The "sample code" you are asking us to write will
have to start with you!

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Robert Morley]

But if he's already filtering out days that the manager has excused, as he
said he is, then there's no problem. That would naturally create a gap in
the consecutive dates, causing the point count to reset as though that day
had been worked normally.

Or have I missed something?


Rob

 

[Robert Morley]

I'm not sure quite how tblEmpSchd fits into this, so I'm ignoring it for
now. If it's for people who work other than M-F, then you'll have to
massively re-write teh IsConsecutive function, and possibly the SickPoints
function. For now, I'm assuming that all employees work M-F. I've also
assumed that tblPointData already has entries in it for each EmployeeID and
Date for the last six months, and only needs to be updated with sick points.

Given that, I would structure the code something like this (this is VERY
light on details and probably won't do everything you need, but should give
you a rough outline to start from...it's also untested, so there may be bugs):

Public Function IsConsecutive(ByVal FirstDay As Date, ByVal NextDay As Date)
As Boolean
'Depending on the structure of your query, etc., you may need to
'expand this significantly to discount holidays, etc.
IsConsecutive = ((NextDay - FirstDay) = 1) Or _
(((NextDay - FirstDay) = 3) And _
(Weekday(FirstDay, vbSunday) = vbFriday) And _
(Weekday(NextDay, vbSunday) = vbMonday))
End Function

Public Function SickPoints(ByVal ThisDay As Date, ByVal PointsSoFar As
Double) As Double
'Returns 1 or 1.5, depending on weekday
'Or returns the difference if more than 2 points accumulated.
'Thus stopping at 2 points.
Dim dblSickPoints as Double
Select Case Weekday(ThisDay, vbSunday)
Case vbMonday, vbFriday
dblSickPoints = 1.5
Case Else
dblSickPoints = 1
End Select

If PointsSoFar + dblSickPoints > 2 Then dblSickPoints = 2 - PointsSoFar
End Function

'Fragment of main code: assumes table is open as recordset called rs,
'ordered by pdEmpID, pdClkIn.

Dim strEmpID As String
Dim dtClkIn As Date

With rs
strEmpID = vbNullString
dtClkIn = 0
Do Until .EOF
If strEmpID <> !pdEmpID.Value Then
dblPtsSoFar = 0
strEmpID = !pdEmpID.Value
dtClkIn = !pdClkIn.Value
End If
If !pdType = "NOP S" Then
If Not IsConsecutive(dtClkIn, !pdClkIn.Value) Then _
dblPtsSoFar = 0
!pdPoints.Value = SickPoints(!pdClkIn.Value, dblPtsSoFar)
dblPtsSoFar = dblPtsSoFar + !pdPoints.Value
.Update
Else
dblPtsSoFar = 0
End
.MoveNext
Loop
End With

As I said, the above will definitely need some testing and debugging, as I'm
writing this completely off the top of my head, and there were things I
didn't necessarily understand. But give it a whirl and see where it gets you.


Rob

 

[TheNovice]

Robert,

There is an old adage that says “Give a man a Fish, he eats for a Day, teach
him how to fish and he eats for a lifetime.

This is my starting point, and I greatly appreciate the push.

Charles Davis