Results 1 to 15 of 15
  1. #1
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50

    Assigning workorders based on volume and skill

    Hello All;


    (not sure if I have this in the correct section, MOD's please feel free to move)

    I'm looking for ideas on how to assign workorders based on volume (of work) and skill level (of the worker).
    I have a table with the workorders (which arrive daily), and lists the difficulty level assigned to the work (1 through 10).
    I have an employees table, which lists employees and their related skill level (1 through 10).
    I want to divide up the work evenly between the employees who are present (changes daily), based on their skill level.
    I have a form that the assigner checks off who is present to do the work that day, and the form counts the checkboxes and totals the number of employees who are here that day.
    I'm stuck on how to divide up the work based on skill level, while keeping the workload as evenly dispersed as possible based on skill level(and accounting for fractional distribution amounts). Understanding that if all (or most) of the work arriving that day requires a higher degree of skill, some will get more work than others who have a lower skill level.

    Anyone have any thoughts, or perhaps seen something like this?

    Thanks...

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    once you have the list of who's present,
    make a query to pull those persons and their skills. qsPersonList
    make a query to pull the work

    a form with 2 list boxes, 1 for each query above, and a 3rd list for who's assigned to which work.

    select a work item in the lstWork list
    dbl-click the person. This event runs an append query to put that person and job into a tWorker table.
    [jobID], [personID]


    similar to the image attached.Click image for larger version. 

Name:	pick state-lbl.png 
Views:	26 
Size:	34.2 KB 
ID:	25633

  3. #3
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by ranman256 View Post
    once you have the list of who's present,
    make a query to pull those persons and their skills. qsPersonList
    make a query to pull the work

    a form with 2 list boxes, 1 for each query above, and a 3rd list for who's assigned to which work.

    select a work item in the lstWork list
    dbl-click the person. This event runs an append query to put that person and job into a tWorker table.
    [jobID], [personID]


    similar to the image attached.
    Thanks ranman... I'll see if I can get it to work. One question though... it seems like you're saying to select a worker, then select individual workorders to assign to that worker. That seems burdensome considering there are possibly 200-300 workorders daily. Perhaps I have misunderstood. I was hoping (if possible), for a VBA solution, such that once I selected the workers, the function (behind a button) would auto-assign based on given parameters.

    Thanks...

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    can you clarify what this means

    I want to divide up the work evenly between the employees who are present (changes daily), based on their skill level.
    Does it mean different skill levels means different work? or perhaps skilled employees can work twice as fast as unskilled employees? or something else?

  5. #5
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by Ajax View Post
    can you clarify what this means

    Does it mean different skill levels means different work? or perhaps skilled employees can work twice as fast as unskilled employees? or something else?
    Yes. Noting to do with speed, or anything else. Work is divided by skill level of the worker. Higher skilled workers get higher difficulty work assigned to them. For instance... some workers can only do oil changes, while others can do oil changes+tune ups, while others yet can do oil changes+Tune ups+transmission work etc. So... an oil change could go to anyone, while a transmission repair can only go to those who have that skill level.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so difficulty level in works orders equates to oil change/transmission work - same as skill level in employees?

    further questions

    if a vehicle in for work of say oil change plus transmission work - so you would go for the higher skilled person to do oil change plus transmission work - or would you go for higher skilled person to do transmission work and lower skilled for the oil change? or to think of it another way, does only one person work on a vehicle? or do many work on a vehicle depending on skill level?

    What about timing issues? key parts not in stock until noon, vehicle not available until 10am?

    what about prioritising work? this vehicle must be finished before that vehicle?

    Best I can guess is in vba/sql you would have something like two recordsets - one for the works orders ordered by difficulty level (high to low) and one for the employees in on the day (does not need to be ordered)

    Code:
    loop through the works orders
         find next employee with right skill level - if not found move first and find next
         assign employee to works order
    next worksorder

  7. #7
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by Ajax View Post
    so difficulty level in works orders equates to oil change/transmission work - same as skill level in employees?

    further questions

    if a vehicle in for work of say oil change plus transmission work - so you would go for the higher skilled person to do oil change plus transmission work - or would you go for higher skilled person to do transmission work and lower skilled for the oil change? or to think of it another way, does only one person work on a vehicle? or do many work on a vehicle depending on skill level?

    What about timing issues? key parts not in stock until noon, vehicle not available until 10am?

    what about prioritising work? this vehicle must be finished before that vehicle?

    Best I can guess is in vba/sql you would have something like two recordsets - one for the works orders ordered by difficulty level (high to low) and one for the employees in on the day (does not need to be ordered)

    Code:
    loop through the works orders
         find next employee with right skill level - if not found move first and find next
         assign employee to works order
    next worksorder
    Ajax...

    Interesting questions. Its not really automotive work, but that was the best analogy I could think of. So, think of the work as a single job, (sticking with the auto analogy) i.e. no car comes in with more than one job to be done. However, prioritizing work would be a plus, but I figured I would do that (prioritize by date/time due) when the worker logs in to get their work.

    I do need to factor for instances where noting but oil changes come in for the day. In those instances I need to distribute the work evenly. My initial thoughts were to divide the total workorders by the number of workers (X), then do the work of distribution based on skill level, with skill level being the primary driver. So I would need to factor in a do-until-X for each of the workers working that day. So a loop within your suggested loop (stopping at X) I think might do it. I think I'll work under that concept a little to see if I can put something together.

    I contacted Mr. Google for perhaps someone who had something like this but he failed me. Maybe its a unique situation perhaps. It has become a challenge for me. I'm not rally a good VBA programmer, but get by with a little help from the forums. The folks that lend a hand are to be commended. I know for myself I really find the assistance helpful, and certainly do appreciate such.

    Thank you !

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I doubt it is a unique situation - the principle requirement is a common one. I think my suggestion is the way to go but would need some refinement - e.g. priority - include time due in sort. You also need to take into account time to do job - no point in saying 80 high priority jobs, each take 1 hour and only 5 people available to do the work - they would have to do 16 hours each.

    provide details of your tables and some example data plus a clear description of the background and I can probably get you going

  9. #9
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by Ajax View Post
    I doubt it is a unique situation - the principle requirement is a common one. I think my suggestion is the way to go but would need some refinement - e.g. priority - include time due in sort. You also need to take into account time to do job - no point in saying 80 high priority jobs, each take 1 hour and only 5 people available to do the work - they would have to do 16 hours each.

    provide details of your tables and some example data plus a clear description of the background and I can probably get you going
    I don't think there will be a situation where the workers are given more work then hours available to complete the work, so that I am not going to factor in. Within the form they work in, they can "re-assign" the work if they feel they won't complete the work. Although new work arrives daily, they have a service level agreement (SLA) of multiple days based on complexity. Plus, if they for instance leave due to whatever, they would re-assign the rest of the work for that day (if approaching SLA close date/time).

    I get raw data in an excel spreadsheet. I pull that into DB table, then do calculations on some of the data to calculate SLA and difficulty rating based on work type. This table [tbl_Workorder_Converted] I have fields for [Difficulty] and [InitiallyAssignedTo] (which is for the worker name once assigned).

    I also have an [tbl_Employees], which has [Name] of course, plus a field for [Skill_Level].

    Once the work assignment is complete, that data will be updated to the [tbl_Workorder_Converted] table. Workers then will log on to their workflow form and complete each job. After completion of the work, they will move on to the next workorder. All forms are unbound (truly unbound). On open (or a "Get Work" button) the workers work will be loaded from a strSQL statement to the [tbl_Workorder_Converted]. Several fields will be loaded, but several more will be completed by the worker, then filed, along with other data, such as date/time completed, worker completing the job, etc.

    I have everything completed and working except this distribution based on job difficulty thing. As of now I could distribute the work simply by workorders/workers=workorders per worker, but then I got stuck on the difficulty level. I am also not sure how I'm going to handle the reminder of say 100 workorders and 6 workers (=16.66 each). I don't have any thoughts on who to assign the .66 workorders to. I guess just wrap back around are reassign the remainder using the same logic in the initial assignment.

    Thanks...

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    good luck with your project

  11. #11
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by Ajax View Post
    good luck with your project
    Thank you... I think I'll need it.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't have any thoughts on who to assign the .66 workorders to
    the loop method I suggested would allocate complete worksorders, so you would not get a 0.66. The issue will be that worksorders are allocated by working down a list - if the list remains in the same order, it will be those at the top of the list who will get the 'last few' so will end up with the extra works order every time. To overcome this, you would need to sort the list on a basis that will mix them up a bit each time you generate the list - might be based on a random number, or might be based on past performance - i.e. order by count of worksorders allocated previously with the lowest number at the top, or some other basis.

  13. #13
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Hello Folks...

    I have just recently re-engaged on this project and have some code that is not working. I could use some help. The code (at least initially) seems to work on the first record, but seems to be not moving to the next record. When it finishes its inner loop (rst4) it goes back to the For A=1 loop line, but then drops to End If. If it were not moving to the next record, it would see (InitiallyAssignedTo = "") as not empty as a result of the very first loop inserting a name. Any help would be appreciated. Thanks.

    And just to recap, the intention is to assign workorders based on difficulty level of the work, and the skill level of the worker.

    ' Open recordsets needed for workorder assignment

    Set db = CurrentDb()
    Set rsemp = db.OpenRecordset(strSQL7) '(tbl_Workers)
    Set db = CurrentDb()
    Set rst4 = db.OpenRecordset(strSQL4) '(tbl_WorkOrders)

    With rsemp '(workers)
    rsemp.MoveFirst 'get the first worker
    Do While Not rsemp.EOF
    ProcessorName = rsemp![Worker]
    ID = rsemp![WorkerID]
    SkLvl = rsemp![SkillLevel]

    'start assigning workorders

    rst4.MoveFirst
    With rst4 '(WorkOrders)
    For a = 1 To PerWorkerWorkOrderCount '(calculated in above non-included code)

    WorkOrderNum = rst4![WorkOrderNum]
    Difficulty = rst4![Difficulty]

    If SkLvl >= Difficulty And InitiallyAssignedTo = "" Then
    InitiallyAssignedTo = WorkerName
    ID_InitiallyAssignedTo = WorkerID

    rst4.Edit 'write assignment data

    rst4![InitiallyAssignedTo] = InitiallyAssignedTo
    rst4![ID_InitiallyAssignedTo] = ID_InitiallyAssignedTo
    rst4![WorWorkOrderStatus] = "N" '(N=New)
    rst4![InitiallyAssignedBy] = (MgrName())

    rst4.Update
    End If

    rst4.MoveNext


    Next a
    End With

  14. #14
    treebark is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    10
    What about a situation similar but I have a slightly different setup:
    100 records (hypothetically) for each record there could be between 1-10 items
    I need to assign the records to 5 employees, but also try to load balance as best as possible, and items cannot be split; so if employee #1 has record #1 they will get all the items for that record.
    I have been able to assign the records as evenly as possible, but having a subsequent process to load balance the items without splitting them from the person who is assigned to the records is my brick wall.
    Thanks

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you are trying to 'hijack' an old thread - very few people will see it. Recommend you start your own thread and explain your problem more clearly and provide detail on your table design and relationships - at the moment you have not asked a question that can be answered

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-02-2018, 11:43 PM
  2. Query Who is Not Trained in a Skill Set
    By londo in forum Queries
    Replies: 5
    Last Post: 08-13-2016, 10:00 AM
  3. Replies: 4
    Last Post: 04-06-2016, 07:17 AM
  4. Replies: 2
    Last Post: 01-01-2013, 06:48 PM
  5. Replies: 1
    Last Post: 02-29-2012, 04:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums