Automatically Shifting Report.

  • Thread starter Thread starter Antonio
  • Start date Start date
A

Antonio

My customer would like to build a scheduling tool into a
current database we have in the office. At the moment,
this database holds Employee information in a table
called "tblEmployeeData". The customer would like a
report that lists all the employees and a corresponding
work schedule (denoted by Weekend (Wed thru Sun 1st
person, Sat thru Wed 2nd person) or Weekday (Monday thru
Friday). However, after a specific amount of time (2
weeks), the report would shift the work schedule down to
the next person in line...and then 2 weeks later, shift
again, etc etc.
Example:
From Jan 1 2004 to Jan 15 2004...("Weekend" denotes Sat
and Sun, "Weekday" denotes Mon - Fri)

txtEmployeeID txtWorkSched
medinaa Weekend
smithl Weekday
johnsonz Weekday
priceo Weekday
dunnl Weekday
razoc Weekend

From Jan 16 to Jan 31

txtEmployeeID txtWorkSched
medinaa Weekend
smithl Weekend
johnsonz Weekday
priceo Weekday
dunnl Weekday
razoc Weekday

From Feb 1 to Feb 15
txtEmployeeID txtWorkSched
medinaa Weekday
smithl Weekend
johnsonz Weekend
priceo Weekday
dunnl Weekday
razoc Weekday

The idea is that each person is on weekend duty for 4
weeks at a time, this is displayed on a report without
actually having to create a record in a shedule table for
each individual person for each individual day. My
customer would like to avoid record entries all together
and just display a specific employee ID in a weekend or
weekday slot for four weeks at a time and rotate through.
I dont know how to set this up....can anyone help.
TIA
 
Antonio:

How I would thing about doing this, is that you could assign for each
individual, in their employee record, some value for the schedule group that
they are on. I.e. Joe might be on schedule 1, Jack on schedule 2. Then if
that schedule group is the current group, they get the weekend duty. To
determine the current schedule group, if you look at the 52 weeks in the
year, there are 13 rotations of 4 week schedules. You can then use the
DatePart("ww",Date()) function to determine the week of the year, and
determine where in each 4 week rotation that you currently are for the
target week you are running the schedule.

e.g. the following would return the rotation group of 1-4 that was current
for the week of datDate

Function GetRotationGroup(datDate As Date)
Dim bWeek As Byte
Dim intPastRotations As Integer
Dim bRotationWeek As Byte

bWeek = DatePart("ww", datDate)
intPastRotations = Fix(bWeek / 4) '= 3 for a date in week 42
bRotationWeek = (bWeek - (4 * intPastRotations))
If bRotationWeek = 0 Then bRotationWeek = 4
GetRotationGroup = bRotationWeek

End Function

You can call that function from the On open event of your report to get the
target rotation group.

From there, you can determine in code in the report if the value of the
employees group number is the same as the RotationGroup returned from the
function, then they've got the weekend duty..... Simply use unbound
controls and evaluate in the On Print event of the detail section as in:

If Me!EERotationGroup = bCurrentRotationGroup Then
Me!UnboundTxtControl = "Weekend"
Else
Me!UnboundTxtControl = "Weekday"
End if

HTH
 
Steve,
Thank you very much for the help.

-----Original Message-----
Antonio:

How I would thing about doing this, is that you could assign for each
individual, in their employee record, some value for the schedule group that
they are on. I.e. Joe might be on schedule 1, Jack on schedule 2. Then if
that schedule group is the current group, they get the weekend duty. To
determine the current schedule group, if you look at the 52 weeks in the
year, there are 13 rotations of 4 week schedules. You can then use the
DatePart("ww",Date()) function to determine the week of the year, and
determine where in each 4 week rotation that you currently are for the
target week you are running the schedule.

e.g. the following would return the rotation group of 1- 4 that was current
for the week of datDate

Function GetRotationGroup(datDate As Date)
Dim bWeek As Byte
Dim intPastRotations As Integer
Dim bRotationWeek As Byte

bWeek = DatePart("ww", datDate)
intPastRotations = Fix(bWeek / 4) '= 3 for a date in week 42
bRotationWeek = (bWeek - (4 * intPastRotations))
If bRotationWeek = 0 Then bRotationWeek = 4
GetRotationGroup = bRotationWeek

End Function

You can call that function from the On open event of your report to get the
target rotation group.

From there, you can determine in code in the report if the value of the
employees group number is the same as the RotationGroup returned from the
function, then they've got the weekend duty..... Simply use unbound
controls and evaluate in the On Print event of the detail section as in:

If Me!EERotationGroup = bCurrentRotationGroup Then
Me!UnboundTxtControl = "Weekend"
Else
Me!UnboundTxtControl = "Weekday"
End if

HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg





.
Click to expand...
 
Back
Top