Results 1 to 6 of 6
  1. #1
    KristenAsh is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    7

    Post How to randomly assign a person (record) based on an entered field in a form.

    I have a list of appraisers that cover certain counties. I need to assign those appraisers to an order based on what county they cover. It seems like this should be simple but I'm a little rusty and I've not sure what the easiest way would be. Would it be just queries or should it be code?

    Here is an example of my list of appraisers.
    County Appraiser
    Monroe ABC Appraisals
    Monroe DEF Services
    Monroe GHI Appraisals
    Cecil 123, Inc
    Cecil XYZ Appraisals



    My main table (orders) is simple. Just an order number, order date, county and appraiser.
    I have a form with entry fields for all these except appraiser. I envision a "assign" button that will then pick the next appraiser in the county that was chosen. It's just a "round robin" type of thing, so the first order placed for Monroe would be assigned to ABC. The next order would be for Cecil and would assign 123. Next order for Monroe would assign DEF.



    I know there are many way this can be done but I've been looking at this for hours and I'm drawing a blank on the easiest way to do it. Can you point me in the right direction?

    Any help is greatly appreciated!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Round robin is different than random, but based on your round robin approach.
    Please see this link for something very similar.

  3. #3
    KristenAsh is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    7
    Thank you for your reply. It doesn't have to be round robin. Random is also fine. I'm just looking for the easiest way to automate assignment. To be in compliance we can not manually pick and choose which company to assign. Any way to let the program do it will work. Would random be easier? I've been out of Access for a while and I'm really rusty.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Need to see all of your tables and it appears only some Appaisers can 'work' on Orders/activities for certain counties. We need more info re number of Order, number of Appraisers and exactly what is an Appraiser and duties in your context?

    It sounds like you have a model similar to the attached jpg.

    Good luck with your Project.

    Here is a routine to find RandomNumbers
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : randomNumber
    ' Author    : Jack
    ' Created   : 11/18/2010
    ' Purpose   : To Generate Random numbers between and including a range of numbers.
    'Lo and Hi are the lowest and highest random numbers you wish to generate.
    'The Randomize keyword is critical to getting different results for each Access session.
    '---------------------------------------------------------------------------------------
    ' Last Modified:
    '
    ' Inputs: N/A
    ' Dependency: N/A
    '------------------------------------------------------------------------------
    '
    Function randomNumber(Lo As Long, Hi As Long) As Long
    10       On Error GoTo random_Error
    20    Randomize
    30    randomNumber = Int(((Hi - Lo + 1) * Rnd) + Lo)
    
    40       On Error GoTo 0
    50       Exit Function
    
    random_Error:
    
    60        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure random of Module AccessMonster"
    End Function
    And a test routine
    Code:
    Sub mytestOfRandomNumber()
              Dim a As Long
              Dim z As Long
              Dim i As Integer
    10        a = 3
    20        z = 300
    30        For i = 1 To 10
    40            Debug.Print randomNumber(a, z)
    50        Next i
    End Sub
    And sample output
    Code:
      208 
     166 
     251 
     76 
     84 
     138 
     195 
     165 
     36 
     126
    Attached Thumbnails Attached Thumbnails AppraisersCounties.jpg  
    Last edited by orange; 02-10-2015 at 07:38 PM. Reason: spelling

  5. #5
    KristenAsh is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    7
    I have two tables - tblAppraisers and tblOrders.

    tblAppraisers is my list of appraisers who are approved to work in specific counties.

    AppraiserID Appraiser County
    1 ABC Appraisals Monroe
    2 DEF Services Monroe
    3 GHI Appraisals Monroe
    4 123, Inc Cecil
    5 XYZ Appraisals Cecil


    tblOrders is the list of "jobs" the appraiser is assigned to.

    OrderID LastName FirstName OrderDate County Appraiser
    13254 Smith John 2/10/15 Cecil 123, Inc.

    I also have a form that is used to enter the new "job" and a button that assigns an appraiser. There are field for everything in the tblOrders table. They enter into into the first five fields, hit the assign button which then populates the Appraiser field and saves the record.

    Nothing else is being done in this database. It is just being used as an assignment tool. Due to compliance regulations, we can't pick the appraiser we want so we just need the program to do that for us. All the work is being done in another system. In the end, we just need the list from tblOrders to show who was choosen by the program so it can be cross referenced to who was actually used in the other system.

    And to answer your other questions, the "job" is a property. The appraiser visits the property to assess the value. The number of appraisers can change (if we approve additional ones for a specific county) but right now, we have about 15.

    I really appreciate your help on this. I know there are many ways to do it but I just need something quick and easy.
    Last edited by KristenAsh; 02-10-2015 at 01:02 PM.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The quickest and easiest may be to simply use the randomNumber code I showed earlier.

    I created another function, that uses the randomNumber function. It requires 2 parameters.
    These are:

    TotalAppraisersForCounty-- a number represent the total appraisers in the County you're working on
    TotalOrderForCountyNotAssigned--Number of Orders for the County not Yet assigned

    You put these numbers into a rouine and it will print out which appraiser gets the Orders.

    This is the code:
    Code:
    Function AssignAppraisersRandomly(TotalAppraisersForCounty As Long, TotalOrderForCountyNotAssigned As Long) As String
    
              Dim a As Long         'always 1  first Appraiser for County
                                    'TotalAppraisersForCounty is total appraisers for this county
              Dim i As Integer
              Dim tmp As String
    10        a = 1
    
    30        For i = 1 To TotalOrderForCountyNotAssigned            'the number of OrderJob for this County not already assigned
    40           tmp = tmp & randomNumber(a, TotalAppraisersForCounty) & ", "
    50        Next i
    60        AssignAppraisersRandomly = Mid(tmp, 1, Len(tmp) - 2)
    End Function
    And here is a test/sample for demonstration.
    Code:
    Sub testAssigningAppraisersToJobs()
    
     Dim totApprsForCounty As Long  'this represents the number of Appraisers for this county
     Dim totOrdersForCounty As Long 'this represents the number of Orders for this county not yet assigned
     totApprsForCounty = 5  '<---example with 5 appraisers for this county
     totOrdersForCounty = 3 '<---example with 3 jobs for this county notyet assigned
     '
     'So you need to assign appraissers to 3 jobs randomly from the 5 appraisers
     '
     'This next line displays the appraisers to be assigned based on the Orders  in your table
     Debug.Print AssignAppraisersRandomly(totApprsForCounty, totOrdersForCounty)
    
    End Sub
    I ran this procedure a number of times to see the random results.
    Here are the results:
    Code:
    1, 3, 2  
    4, 3, 4
    3, 3, 4
    4, 4, 1
    4, 5, 2
    4, 2, 3
    3, 3, 5
    2, 1, 4
    Looking at the first result 1,3,2 means you assign
    Appraiser 1 to first Job/Order
    Appraiser 3 to the second Job/Order
    Appraiser 2 to the third Job/Order

    You could use a Form with 2 text boxes or 2 combos. These would be for the Number of Appraisers and the Number of Jobs respectively. With text boxes you would enter the numbers; with combos you could select values from the combo. In either case the just represent the number of Appraisers for the county, and the number of Jobs for the county. You push a button and it calls the function behind the scenes and displays the output in another textbox or message.
    You would update your tables as shown above. You can certainly get more sophisticated with more programming.
    But this would probably handle quick and easy.

    Good luck.
    -

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

Similar Threads

  1. Replies: 3
    Last Post: 01-09-2015, 05:48 PM
  2. Replies: 2
    Last Post: 09-24-2014, 05:19 PM
  3. Replies: 5
    Last Post: 05-10-2014, 01:14 PM
  4. Replies: 2
    Last Post: 11-30-2013, 02:53 AM
  5. Replies: 5
    Last Post: 04-16-2013, 07:24 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