Results 1 to 12 of 12
  1. #1
    trevor is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2018
    Location
    Orlando, FL, USA
    Posts
    10

    Question Dividing a list of task between users.

    Heya party people!



    I have an access database that we're using to assign task to users that they will update with the status of the task. We will be importing data that will add each task as a new row on various tables.

    I have a table named "Responsibility", that contains all of the imported data and information. Two of my fields in this table are "Assigned To" and "Status".

    The "Assigned To" field contains a number that correlates to the primary key on the table "Users". The "Status" field defaults to "Incomplete".

    Would it be possible through VBA or another method to have a form where I can check a list of users and have it search the Responsibility table for rows that have "Incomplete" or blank statues and divide the task between the assigned users?

    So lets say I have 100 records and 6 users with the following IDs:

    User1:1
    User2:3
    User3:4
    User4:12
    User5:15
    User6:7

    could it go through and assign them in order like 1,3,4,12,15,7 till it ran out of records?


    Thanks in advance to any advice you can offer. I've been learning Access over the past few months and it seems awesome so far, just a few tricky questions here and there.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    909
    Very possible. Can you post a screenshot of your table relationships? What's your experience/comfort level with VBA?

    How are the tasks assigned to new users? Randomly?

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    909
    I slightly misunderstood your question. But here is some example VBA code that should get you started. As I don't have all the details of your database this is entirely untested and I can't vouch for this being bug free.

    Code:
    Private Sub AssignIncompleteResponsibilities()
    On Error GoTo ErrHandler
        Dim rs As DAO.Recordset
        Dim qry As String
        Dim users() As Long 'An array to hold user IDs that you want to assign tasks to
        Dim user_index As Long 'track which user we're assigning tasks to
        
        
        '*********************************************************************************************
        'YOU NEED A METHOD TO POPULATE THE users() ARRAY WITH THE USER IDS YOU WANT TO ASSIGN TASKS TO
        'THIS CAN BE A HARD CODED LIST, ANOTHER VBA FUNCTION, A PARAMETER ARGUMENT, ANOTHER QUERY
        ReDim users(1 To 6)
        users(1) = 1
        users(2) = 3
        users(3) = 4
        users(4) = 12
        users(5) = 15
        users(6) = 7
        '*********************************************************************************************
        
        user_index = 1 'start at one for base 1 array, or 0 for base 0 array, etc.
        
        'Query all the records with an incomplete status. 
        'Careful, using this particular query will reassign responsibilities that have already been assigned. I'm not sure that's what you wanted or not?
        qry = "SELECT [Assigned To] FROM [Responsibility] WHERE [Status]='Incomplete';"
        
        'Load the query
        Set rs = CurrentDb.OpenRecordset(qry, dbOpenDynaset)
            
            'Make sure we actually found records or we'll get errors
            If Not (rs.BOF And rs.EOF) Then
                
                'This line probably redundant but can't hurt
                rs.MoveFirst
                
                'Loop through each of the records from the query
                Do While Not rs.EOF
                    rs.Edit
                    rs![Assigned To] = users(user_index)
                    rs.Update
                    
                    user_index = user_index + 1 'move on to assign to the next user in the users array
                    If user_index > UBound(users) Then user_index = 1 'roll back to the first user if we just assigned to the last user in the users array
                    
                    'Move on to the next incomplete task, rinse and repeat
                    rs.MoveNext
                Loop
            End If
            
        'always close your objects
        rs.Close
    
    ExitHandler:
        'release your object variable
        Set rs = Nothing
        Exit Sub
        
    ErrHandler:
        MsgBox Err.Description, vbExclamation, "Error Reassigning Tasks #" & Err.Number
        Resume ExitHandler
    End Sub

    This will find all records with Incomplete status. You said incomplete or "blank". Do you mean Null? Does your [status] field allow for zero length strings? Would you want this work on all Incomplete/null tasks or just Incomplete/Null tasks that are also unassigned? For example:
    Code:
    qry = "SELECT [Assigned To] FROM [Responsibility] WHERE ([Status]='Incomplete' OR [Status] Is Null) AND [Assigned To] Is Null;"

  4. #4
    trevor is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2018
    Location
    Orlando, FL, USA
    Posts
    10
    Here are the relationships I have so far. I'm still at the start of this project and have yet to add all the data.

    This is actually a rebuild of a project I threw together a few months ago with a lesser degree of skills, I've been reorganizing and recode my VBA to make the entire project more structured while adding improvements for the new version.

    I don't have the most experience with VBA. So far I've been doing okay, I've learned more and more as I've moved on with the project.

    Click image for larger version. 

Name:	Table Relationships.jpg 
Views:	22 
Size:	113.2 KB 
ID:	35224

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    909
    In that case replace the qry string from my code in post #3 with something like this:
    Code:
    qry = "SELECT [Assigned To] FROM [Recalls - eResponsibility] WHERE [Status]='Incomplete' or [Status] Is  Null;"
    or this:
    Code:
    qry = "SELECT [Assigned To] FROM [Recalls - eResponsibility] WHERE [Assigned To] Is Null AND ([Status]='Incomplete' or [Status] Is Null);"
    For the record it's best practice NOT to have spaces in your field names, query names, table names, etc. Just use letters, numbers, and underscores. For example [Assigned To] becomes AssignedTo or Assigned_To (or better yet UserID)

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    909
    Also, you might consider changing the status field from a text field to a number field that is a foreign key linked to a status types table. And don't be tempted by Lookup Fields!

  7. #7
    trevor is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2018
    Location
    Orlando, FL, USA
    Posts
    10
    Thank you!

    Quote Originally Posted by kd2017 View Post
    This will find all records with Incomplete status. You said incomplete or "blank". Do you mean Null? Does your [status] field allow for zero length strings? Would you want this work on all Incomplete/null tasks or just Incomplete/Null tasks that are also unassigned? For example:
    Code:
    qry = "SELECT [Assigned To] FROM [Responsibility] WHERE ([Status]='Incomplete' OR [Status] Is Null) AND [Assigned To] Is Null;"
    The field does allow for zero length strings at the moment, I think it would make sense to change that.

    Quote Originally Posted by kd2017 View Post
    Also, you might consider changing the status field from a text field to a number field that is a foreign key linked to a status types table. And don't be tempted by Lookup Fields!
    Quote Originally Posted by kd2017 View Post
    For the record it's best practice NOT to have spaces in your field names, query names, table names, etc. Just use letters, numbers, and underscores. For example [Assigned To] becomes AssignedTo or Assigned_To (or better yet UserID)
    That makes a lot of sense! Less room for data type mismatches and it'll take up less space. Still a lot of learning for good practices! So far Access seem pretty great, plus with VBA is easy to learn from examples as long as you have an understanding of SQL.

    I'm going to give these a shot and see how it goes!

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,727
    Typically tasks can vary in the amount of effort required (eg 1 hr, 2 person-days...), so is there some quantifier for your tasks?

    If the tasks all require same amount of effort (roughly), then you could assign them round robin. If there is some necessary sequence/order to the list of tasks, then you must account for that.

  9. #9
    trevor is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2018
    Location
    Orlando, FL, USA
    Posts
    10
    All of the task will take about the same amount of time.

    This program will be used to assign outbound calls to a call center. When we load the list in for different calls such as recalls, missed appointments and so on we will assign them to people to call.

    The issue being there are list of 5,000+ people to call. My idea is to have a button the managers can click to bring up a menu where they can select the type of calls to assign. We'll have a box where we can input the number of people to call, say 2000 a week; and check the reps to break these calls up to.

    They will then be able to make the result of the call and it will automatically move to the next call to place. With click to call buttons and one click resources to lookup information this should help increase the number of calls we can do and help us track who has calls not completed.

    The current method is email them spreadsheets to make notes on and they email them back.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,727
    You are working with a list that has some how identified
    the type of calls to assign
    .
    How is that determined and done?
    What is the general workflow such that required calls(~5,000) are determined/recorded, analyzed, assigned a type attribute, and then assigned to "users/processors"?

    You know your set up and culture better than readers.So, depending on your analysis and mandate your may want to review this material from Phil re Queue based applications.


    Good luck with your project.

  11. #11
    trevor is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2018
    Location
    Orlando, FL, USA
    Posts
    10
    Quote Originally Posted by orange View Post
    You are working with a list that has some how identified .
    How is that determined and done?
    What is the general workflow such that required calls(~5,000) are determined/recorded, analyzed, assigned a type attribute, and then assigned to "users/processors"?

    You know your set up and culture better than readers.So, depending on your analysis and mandate your may want to review this material from Phil re Queue based applications.


    Good luck with your project.
    Thank you for the read, very good article for this situation. The calls to be placed in this example comes from a list we receive from the manufacturer of customers in our zone affected by the recall. I'm importing this data in to access from an excel spreadsheet. Then we will assign a set number of calls from the list to reps to contact those customers to try to set an appointment.

  12. #12
    trevor is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2018
    Location
    Orlando, FL, USA
    Posts
    10
    Thank you again for the help, it seems to run okay however I fear I may be implementing something wrong.

    I changed the Assigned to and status field from a short text to a lookup field. However, when running the code now, it will go through the array of IDs I've entered in there for now but it seems to skip an equal number of rows after assigning them instead of looping back to the first ID in the array.

    Click image for larger version. 

Name:	Capture.jpg 
Views:	9 
Size:	70.6 KB 
ID:	35231

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

Similar Threads

  1. Replies: 2
    Last Post: 06-24-2016, 05:46 PM
  2. Replies: 2
    Last Post: 11-06-2015, 07:57 AM
  3. Replies: 4
    Last Post: 11-04-2015, 05:44 AM
  4. Replies: 7
    Last Post: 05-06-2015, 01:04 PM
  5. Replies: 13
    Last Post: 11-17-2013, 03:33 PM

Tags for this Thread

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