Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Issue Jobs Evenly - a little challenge

    I have an idea, but don’t know if it can be done,

    I am creating a helpdesk and am at the stage of allocating jobs to technicians.

    I would like to ensure that the tasks are spread out evenly between the technicians, for example:

    I have 4 Technicians and 100 jobs; I would like the jobs to be allocated 25 each,

    As the jobs are cleared that technician’s job list will decline.

    In the meantime new jobs may come in I would then want these added to the technicians so that they become evenly spread out again.

    Is this as complicated as it sounds…….

    A nice little example would be nice please…..

  2. #2
    Join Date
    Mar 2004
    Location
    Sweden
    Posts
    11
    Pardon me for saying so, but it sounds simple enough. I assume you have a table with the joblist. Just add a new field: Technician (if you haven't already) and a yes/no field: Cleared

    The technician gets his joblist through a query/filter (his ID or name or whatever that's put in the field Technician and Cleared = no)

    Now all you have to do is figure out how to determine which technician has least ongoing jobs so that when a new job is entered it goes to that guy. I'd guess som sort of query liske this... (assuming the table is called JobList, paste into SQL view in query design)

    SELECT TOP 1 JobList.Technician
    FROM JobList
    GROUP BY JobList.Technician
    HAVING (((JobList.Cleared)=No))
    ORDER BY Count(JobList.Technician);

    ...would result in a list of technicians having the lowest workload.

    Of course you'd like to choose and insert the technician automatically. I guess a bit of code could do that (use the SQL statement from above in your code and use it with OpenRecordset - if I remember rightly - can be a bit hairy if you're not used to it). Possibly there are other simpler ways of doing it.

    Good luck!

  3. #3
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Issue Jobs Evenly - a little challenge

    This can be done but with a change. say there are 100 jobs and 4 techs. each one got 25. after that each tech closed some of the jobs now the situation is
    tech1 =20
    tech2=25
    tech3=20
    tech4=15

    total of 80 jobs still open.

    now you get 12 more new jobs.

    Here is the turning point. Do you want to distribute all 12 jobs evenly among the 4 techs ie 3 jobs each OR you want to balance the workload. this would mean the tech who was more efficient and completed 15 jobs will again get the bulk of the new jobs.

    If you want to go with the latter then this how you can accomplish the task.

    Get the % closed job of each tech. ie.
    Tech Closed Jobs %
    1 25-20=5 5/20*100=25%
    2 25-25=0 0/20*100= 0%
    3 25-20=5 5/20*100=25%
    4 25-15=10 10/20*100=50%

    Now use these percentages to the new jobs (12 new jobs) to distribute that is
    tech1 12*25%=3
    tech2 12*0%=0
    tech3 12*25%=3
    tech4 12*50%=6

    which will make the work load of each tech as
    1 20+3=23
    2 25+0=25
    3 20+3=23
    4 15+6=21



    Originally posted by Chimp8471
    I have an idea, but don’t know if it can be done,

    I am creating a helpdesk and am at the stage of allocating jobs to technicians.

    I would like to ensure that the tasks are spread out evenly between the technicians, for example:

    I have 4 Technicians and 100 jobs; I would like the jobs to be allocated 25 each,

    As the jobs are cleared that technician’s job list will decline.

    In the meantime new jobs may come in I would then want these added to the technicians so that they become evenly spread out again.

    Is this as complicated as it sounds…….

    A nice little example would be nice please…..

  4. #4
    Join Date
    Mar 2003
    Posts
    225
    Sounds great, but not to sure how i would do this,

    i have attached my DB so you can hopefully see how i am planning on storing the info,

    cheers

    Andy
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    I'll ty to take a look at your db oday otherwise check back on monday...

  6. #6
    Join Date
    Mar 2003
    Posts
    225
    thankyou really appreciate it

  7. #7
    Join Date
    Mar 2003
    Posts
    225
    Hi i was just wondering if you have had a chance to look at this problem yet please.

    Cheers

    Andy

  8. #8
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    I did glanced over it but didn't had time to go into details. Looks like you are using macros !!! not a good approach. I'll try to give some time to it. and will post it here as soon as I can.


    Originally posted by Chimp8471
    Hi i was just wondering if you have had a chance to look at this problem yet please.

    Cheers

    Andy

  9. #9
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi

    I noted KHAN's resolution to the work-load issue

    first time Ive seen it like that - looks a good way to deal in a work through especially taking the load issue in to account first

    Only issue i have is whist spreading the load is labour friendly - care on the LOCATION of the task MUST be high in agenda. If a tech is working on-site somewhere it could the another is closer to the brakedown and although tech one only has 80% load and therefore should be next allocated tech two with 90% is closer and will spend less man-hours getting to the fault


    Just a thought thats all


    regards

    gareth

  10. #10
    Join Date
    Mar 2003
    Posts
    225
    hi, just wondering if there was any further thoughts on this problem please

  11. #11
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    hello

    Ive thought about this one a bit, basically because its running very close to a UK originated system called CARE. This in effect automates the job allocation system BUT as pointed out its not aall singing and dancing application. I think you need to consider khans resolution - it looks good to me, then possibly look at the user being responsible for the location issue - that way it will remove the DB's black and white way of dealing with the job allocation and allow greater flexibility

    I know this doesnt seem to fully answer your last query but i think once you have a 1st plateform in use you'll naturally see areas of improvement without massive backround changes to you system


    hope this helps some

    gareth

  12. #12
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    I'm so sorry ppl, my boss is just killing me with loads of projects. I am just not getting time to do all the programming for you. I hope someone will pick the idea I gave and will help you out with some code. I've downloaded it and if and when I get time to work on it, will do that.


    Originally posted by Chimp8471
    hi, just wondering if there was any further thoughts on this problem please

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •