How do I create staff scheduling in ACCESS

[GreenAcres]

I want to create a system in ACCESS where I can enter staff data and schedule
time, days off, etc.

 

[Duane Hookom]

This sounds like a fairly ambitious project. How far have you gotten? Do you
have some specific questions?

 

[GreenAcres]

Thanks for responding! I have created the database and entered all of the
staff data, i.e., workdays, offdays, shifts (we have 3) vacation information.
Now I need to figure out how to automatically schedule staff daily based on
this data.

 

[Duane Hookom]

Have you recorded all of your business rules that pertain to staff
scheduling? This would be rules like the order that OT is handed out,
seniority in scheduling, maximum and minimum hours per week, how to handle
preferences,...

 

[GreenAcres]

No, I haven't. Have you done anything like this? I am fairly new to ACCESS.
Can you give me a few basic pointers on how to begin this? Thanks in
advance!

 

[Duane Hookom]

I have done something similar a couple times. One application schedule
athletic leagues and the rules were fairly clear to schedule round robin
league play.

The other scheduling application was for scheduling production in a factory.
The "involved staff" at the factory never could fully describe what they
needed. We found that if they wanted to consider a specific rule, they had
to enter information to be used by the rule. They mostly dragged their feet.

I would probably not attempt to take on a project like this again without
someone creating complete specifications and signing off on them.

 

[tonyjean]

Hi :

I have to do the same thing. I'm wondering if any one out there, has done
an ACCESS database that would do staff scheduling. As I am also fairly new
and have some but not much VB programming skills I am not sure where to
start. I have created a DB which has employees, Departments etc and wish to
integrate the staff rotations into it. We have 3 basic rotations ( 5days
worked 2off, 10 days worked 4 off, and 16 on and 6 off. If someone can help
me I would be willing to buy the code, as I am looking at many hours trying
to figure this one out. Is this beyond a beginner?
Would appreciate any feedback.
Thanks in advance.

 

[RFGunManRos]

GreenAcres,

I have a staffing database that tells whom is scheduled and what job
functions they will be doing for the day etc. on a rotation basis. It is
incomplete but I'm working on some kinks (Like after two weeks of running
schedules I'm getting a 1066 error I believe that says duplicate records even
though I have a bypass for it.)

I would be willing to share it but its pretty simple to do yourself as
follows.

Tables:

tbl_Emp
(This is employee names, ID's, day off number (1 = Sunday and Saturday = 7)
as well as the Job category that they fall under based on the third table
downs rules, last the shift they work (ie: 1, 2, 3)

tbl_Team_Day_Off
(this is the Day "1", and the team that would apply to (Sun)"A" and Day 4
(Wed.) team D) (I have my teams setup based on best to worst as follows:
A=Friday, B=Monday, C=Wednesday, D=Tuesday, E=Thursday) Saturday and Sunday
are not needed in my case as I dont work weekends period!) (For each one you
need a yes/no and select if day off or not)


tbl_Job_Functions
(Each Job function has a Job Category (Department Name or Department Code)
and a Job Function (This is the different positions I need to fill each day)

Now start building queries and linking your fields relationships and get the
data you want. I then made it a mktbl and started a table (Holding the names
and job function for a single day only) then changed it to an appndtbl for
future runs and testing)

This is where it gets tricky is the code to fix the duplicate date errors
etc. But here is my code to start with.. Like I said it has an error on
duplicates but I'm sure you can work it out. I'm on another project now an
djust have set this aside for a month or so. I'm in the proces of building a
sytem to track occurences from a time clock adn issue HR documents Auto as
well as issue write ups based on returning equipment habits. I may be close
to finishing but I have to work on it before i can fix these other things you
need...


Best of Luck

Kurt

--------------------------Module1 CODE
START-----------------------------------


Option Compare Database

Sub mcrSetupSchedule()
On Error GoTo errTest
Dim dbs As DAO.Database
Dim rstEMP As DAO.Recordset
Dim rstJOB As DAO.Recordset
Dim rstEmployeeAssignment As DAO.Recordset
Dim tbl As DAO.Recordset
Dim strEMP As String
Dim strJobCat As String
Dim strJobFun As String
Dim rstJobCat As DAO.Recordset
Dim dtmStart As Date
Dim dtmEnd As Date
Dim bytDayOff As Byte
dtmStart = Now() - 1
dtmEnd = #6/9/2006#

Set dbs = CurrentDb
Set rstEMP = dbs.OpenRecordset("SELECT DISTINCT Badge_Num, Job_Category,
Day_Off_Number, Shift " & _
"FROM tbl_Employee_Schedule;")


With rstEMP
.MoveFirst
Do Until .EOF
strEMP = .Fields("Badge_Num").Value
strJobCat = .Fields("Job_Category").Value
Set rstJobCat = dbs.OpenRecordset("SELECT Job_Function "
& _
"FROM
tbl_Job_Assignment_List " & _
"WHERE Job_Category='"
& strJobCat & "'")
rstJobCat.MoveFirst
Do Until rstJobCat.EOF
strJobFun = rstJobCat.Fields("Job_Function").Value
dtmStart = dtmStart + 1
bytDayOff = Weekday(dtmStart)
'1 skip Saturday and Sunday
'determine if weekday(dtmStart) = 1 then add 1
(Sunday)
'if weekday(dtmStart) = 7 then add 2 (Saturday)
If Weekday(dtmStart) = 1 Then 'Saturday or Sunday
dtmStart = dtmStart + 1
ElseIf Weekday(dtmStart) = 7 Then
dtmStart = dtmStart + 2
End If

If bytDayOff = .Fields("Day_Off_Number").Value Then
dtmStart = dtmStart + 1
End If


'2
'also ensure that no individual has duplicate Job
Functions for a given day and shift
' DoCmd.RunSQL "INSERT INTO tlnkEmployeeAssignment "
& _
' "VALUES(" & .Fields("Shift").Value &
",[" & strJobFun & " ],'" & dtmStart & "'," & strEMP & ")"

Set tbl = dbs.OpenRecordset("tlnkEmployeeAssignment",
dbOpenDynaset)
tbl.AddNew
tbl.Fields("Shift").Value = .Fields("Shift").Value
tbl.Fields("Badge_Num").Value = strEMP
tbl.Fields("Job_Function").Value = strJobFun
tbl.Fields("Date").Value = dtmStart
tbl.Update
Debug.Print .Fields("Shift").Value & ": " & strEMP &
": " & Format(DateAdd("d", i, dtmStart), "Short Date") & ": " & strJobCat &
": " & strJobFun
lala:
rstJobCat.MoveNext
Loop
.MoveNext
dtmStart = Now() - 1
Loop
End With

Exit Sub
errTest:
If Err.Number = 3021 Then 'no such record is found
Resume Next
ElseIf Err.Number = 3022 Then 'primary key violation
rstJobCat.MoveNext
strJobFun = rstJobCat.Fields("Job_Function").Value
Set tbl = dbs.OpenRecordset("tlnkEmployeeAssignment", dbOpenDynaset)
tbl.AddNew
tbl.Fields("Shift").Value = rstEMP.Fields("Shift").Value
tbl.Fields("Badge_Num").Value = strEMP
tbl.Fields("Job_Function").Value = strJobFun
tbl.Fields("Date").Value = dtmStart
tbl.Update
Debug.Print rstEMP.Fields("Shift").Value & ": " & strEMP & ": " &
Format(DateAdd("d", i, dtmStart), "Short Date") & ": " & strJobCat & ": " &
strJobFun
On Error GoTo errTest
GoTo lala
Else
MsgBox Err.Number & vbLf & Err.Description
'Resume
End If
End Sub