Watchbill/Sort of employee shift scheduler advice

[ddecker3]

Hello,

I am trying to figure out a way to make an automated watchbill.

ok brief descp. for the non military. We are in 4 sections. Each section has about 20 people. We stand 2 differant watchs in 8 hour intervals. So say Monday Duty Section 1 has it, so they need 6 watchstanders total. 3 for the office, 3 for driver. Tuesday section 2, 6 people.

Ok so thats explained now.

Here is what I have so far

Tables
Personel
Excuses - Used for Vactions/Sick/School.
Watch

peronsel fields
SSN (last4) - PK
Last Name
First Name
Rate
Watch
Duty Section

Excuses
SSN
Last Name
Watch
StartDate
EndDate

Watch
This one is the one I am lost for.
I know i have 2 watchs, but there is 6 a day, ect So not to sure how to do that.

I have setup simple querys for duty sections, excuses, watches ect that make life easier. But still no closer to trying to create the watchbill.

In a perfect world I would like it to fill out each watch for the duty section, depending on excuses, and validating it has not used the samer personel twice, in a great world I would like it to verify against the last watchbill for that section to verfy the same personel do not have a watch two duty days in a row.

I know this is alot, and I could have possibly broken it down, but was unsure of where to break it at.
Any help would be appreciated.

 

[Darrylles]

ddeck

Now describe exactly what you need in English only. Don't bother with your current table structures etc (they are wrong anyway).

The tables that you have described give a good idea of data requirement - now explain exactly what you want to record (try replacing army jargon with 'real' meanings, e.g. what the hell is a 'watchbill?).

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[ddecker3]

Ok,

A Watchbill is like an employee schedule for a day.
We have 20 employee's that can work each day in a four day rotation. But we only need 5 people during the work week, and 6 people on the weekend.

There is 2 watches(jobs) that are eight hour shifts each.
One is called ASDO, and the other is Duty Driver.
In a work week ASDO position will work two shifts.
1600-2400, and 2400-0800.

Duty Driver has three positions;
080-1600, 1600-2400, 2400-0800.

Monday - Section 1 - 10 Qualified Asdo / 10 Qualfied DutyDriver.

Tuesday - Section 2, - 10 Qual. Asdo /10 Qual DD

Wensday - Sect 3

Thursday Section 4

Friday Section 1

Saturday Sec 2

and so on each duty section has on average 10 qualified ASDO. and 10 qualified Duty Drivers.

The excuse log is just for people to put in time off the watchbill for leave, school, sickness, baby, ect.

I hope that explains it all.

-----------
Now what I would like it to do is somehow generate and watchbill for each duty section automatically. Say i select Duty Section 1. It would autofill the required watch's. But I wanted it to verify against Duty Section 1's last duty day so no one should get two duty days in a row with a watch. And also take the excuse log, and remove the names depending on the date and duty section from available choices for the watchs.

I really hope that explain is all It makes perfect sense in my head.

David

 

[Remou]

I would imagine that you should first select all available people, something like (air SQL):

[tt]SELECT Id, Name, CanDrive FROM tblPeople
WHERE Id NOT IN (SELECT Id FROM tblExcuses
WHERE ExcuseEndDate Is Not Null)
AND Id NOT IN (SELECT Id FROM tblWatch
WHERE WatchDate =Date()-1)[/tt]


You can now use a little code to randomly select the required number for the watch. Here is an outline of using Rnd to get a list of IDs, it is not tested:

    Randomize
    'rs = recordset of people available for driving or watching
    'you will need two.
    rc = rs.RecordCount
    'Number of people to pick
    intNeeded = 6
    
    Do Until Len(IDList) - Len(Replace(IDList, ",", "")) = intNeeded
        x = Int((rc * Rnd) + 1)
        rs.MoveFirst
        rs.Move x
        varID = rs!ID
        IDList = IDList & IIf(InStr(IDList, varID) > 0, "", varID & ",")
    Loop
    IDList = Left(IDList, Len(IDList) - 1)
    Debug.Print IDList

 

[MajP]

You show two watches with:
ASDO: 2 shifts
Duty Driver: 3 shifts

But you talk about 5 people needed, 6 people needed and different numbers on the weekend. This is not clear.

We stand 2 differant watchs in 8 hour intervals. So say Monday Duty Section 1 has it, so they need 6 watchstanders total. 3 for the office, 3 for driver.

and then you say

But we only need 5 people during the work week, and 6 people on the weekend.

Explain what is correct.

Anyways, I would first build a shift table with all shifts in it.

tblShifts
strShiftID
strWatchType
dtmStartTime
dtmEndTime

the data would look something like

ASDO1 ASDO 1600 2359
ASDO2 ASDO 0001 0800
DutyDriver1 Duty Driver 0800 1559
DutyDriver2 Duty Driver 1600 2359
DutyDriver3 Duty Driver 0001 0800

If there are additional watches on the weekend you would have to add them. Note the times are going to be key. You are going to have to do a lot of date and time checks so (0001 versus 2400).

I assume in your personnel table that "Watch" is which watch they can stand (hopefully no one can stand both type of watches or you will need another table.)

Ok the next thing is the Watch Bill Assignments table. I think you need the following:

tblWatchBillAssignments
autoAssignmentID
SSN (person id)
strShiftID (from tblShifts)
dtmStartDate (the day and possibly time the shift starts)

Now here is the pseudo code, but will take a while to write. This is not trivial.

1. Determine what day to start at (either the last day assigned or as user determined)

2. Determine the last section to do duty
3. Calculate next section
4. I would return a recordset that returns how many times each person in that section has stood duty and sort lowest to highest.
5. Start first with personnel able to stand ASDO
6. Draw the first person off of the list (lowest amount of times stood duty), check to see if they stood the prior duty day, check to see if they have an excuse, assign to first avaialable shift. Write to tblDutyAssignments
7. Continue down the list until all ASDO shifts assigned
8. Go to personnel available to stand duty driver
9. repeat 6
10. calculate next section, move to next day.

I personally do not think this is a trivial procedure to get a working project. I think this would take me several hours to code and test. If you are not VBA savvy this may be very difficult.

 

[ddecker3]

Let see there are two watch's (positions). ASDO and Duty Driver.

Weekdays ASDO has two shift.
Duty Driver has 3 shifts.

Weekends the both have 3 shifts.


Hope that explains it all.

 

[MajP]

In a work week ASDO position will work two shifts.
1600-2400, and 2400-0800.

What are the times for the weekend shifts? I assume it is 0800-1600, 1600-2400, 2400-0800

If you are still interested in doing this, I will work some code.

 

[ddecker3]

Thats correct the watchs on the weekend are those.
Yes I am still very interested in trying to get something working, its fun trying to learn things as I go along also.