Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    29

    Red face Unanswered: Access 2002: How would I do this?

    I'm trying to think logically (and it's late) heh. Anyway, I'm trying to assign work on an automated basis

    I have employees that can only do A,B, and/or C tasks, and I want to create a function to asssign the tasks to them.

    I have a table of tasks that fall into A,B or C catagory... and a Table of employees that fall into A,B, and/or C catagory.

    How do I select a specific employee for a 1 to 1 (based on Task_ID & Employee_ID) relationship based on this Many to Many relationship (Based on Task_Cat & Emp_Task_Cat) I currently have?

    I DO want to keep the number of tasks evenly distributed over all the employees, and prevent employees that do multiple types of task from getting just that much more work.

    How would any of you go about this?

  2. #2
    Join Date
    Mar 2002
    Posts
    32
    Well it's going to take some coding.

    You're going to need a field in Tasks that's yes/no. Also you need a field in Employee's that's task ID (Perhaps multiple fields).

    Code a loop that selects the first employee record...then makes a recordset of all available tasks of the appropriate type that are unassigned. Grab the first record from that recordset, and set it's assigned flag to yes, and set the Employee's task field to Task ID. Take the next employee and repeat the loop until out of tasks or employees.

    If you make a second or third task field then you can loop again and fill those in.

    You can then make your relationships one to one based on task ID.

    Just my thoughts...let me know if you need any coding help.

  3. #3
    Join Date
    Mar 2004
    Posts
    29
    Thanks, that sounds like a good suggestion, however I'm finding that I will have to have these tasks be able to be assigned and tracked over a long period of time.

    I was thinking that I could use a crosstab query to populate a field in the employee table with a total of assigned tasks and then select the record with the least number in that total column (filtered by type of task to be done) and assign that one.

    The problem I run into is that since I'm going for equilibrium ideally if everything works I run into all employees having the same number of tasks assigned so that doesn't work.

    I also want to cap the number of tasks assigned to an employee that were created on the same day. For instance if there are 80 tasks from the 1st and I add a new employee he or she would not get ALL 80 tasks (with a 48hr deadline) just by virtue of the fact that they had none in que. The daily cap would be an arbitrary number assigned by me for each employee. (Once a task is assigned it creates a new record in a third table to keep track of them all and that is where the Count would come from.)

    Now, I have created something like this that works for one Task at a time but my problem is limited prog. expierience and the fact that I see no way to loop something like this without the logic breaking down.

    I thought about basing the rotation on the autonumber in access to just set a random rotation order and do an update that goes something like...
    Autonumber + Count of Employees = Bottom of selection list, however this ignores the total of active volume that each employee would have.

    So you see where I'm going with this I hope (cause the more I think on it the less I do)....

  4. #4
    Join Date
    Mar 2002
    Posts
    32
    Doh!

    My bad...it would be a better idea to do as I suggested above, but instead of adding Task ID to the employee record....add Employee ID to the Task Record

    That Crosstab thing you are explaining has me a wee bit confused

Posting Permissions

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