Create query to divide work among employees

[bcmarshall]

I have been asked to create a routine to divide a list of customers among several employees for them to be followed up. For simplicity, imagine a list of 100 records to be split among 7 people.



My thought was that if I could run a query that would assign the numbers 1-7 to each record in the set in sequence and repeat that sequence until the end, then of course it's easy to sort all the 1's and send them to Joe and all the 2's go to Sally and the records would be evenly divided. I'm imagining this as some sort of counter within the query. There is no specific ordering involved. All records and employees are considered to be equal in this case, and we just want to randomly divide them up.

This concept is just a suggestion, but it may not be the ideal way to accomplish my goal. I'm not trying to limit the solution to one possible framework.

Can anyone suggest a way that will provide the distribution that is required?

Thanks in advance for any assistance you can offer.

[orange]

If the current order is not significant, then you approach seems reasonable.
If there is some inherent order in the existing records, you could add a random number and sort the records and assign same base of the random number. Again it would be rotating from employee1 to employee7 but the assignments would be "random".

[bcmarshall]

Can you provide a means to generate that sequential number in the query so that the records carry a number from 1-n sequentially and repeatedly through the set? Once I have that numbering figured out the rest is a piece of cake!

[Bob Fitz]

Do you have to use a query?
You could do in in a module using a recordset.
Something like:

Public Function fn1to7()

    Dim rec As Recordset
    Dim i As Integer
    
    Set rec = CurrentDb.OpenRecordset("T1")
    i = 1
    With rec
        Do Until .EOF
                
            If i < 8 Then
                .Edit
                .Fields("Num") = i
                .Update
                i = i + 1
            .MoveNext
            Else
                i = 1
'            .MoveNext
            End If
            
        Loop
    End With
    
    Set rec = Nothing
End Function

Where "T1" is the name of your table and "Num" is the name of the field that is to hold the value 1 - 7

[orange]

You could use this as a concept:

Sub createrecs()
'assuming 100 records and 7 employees
'assign records to employees
Dim irec As Integer
Dim rec As String: rec = "record"
For irec = 1 To 100
  Debug.Print rec & irec & " assign to employee " & irec Mod 7 + 1
Next
End Sub

Will give a list

record14 assign to employee 1
record15 assign to employee 2
record16 assign to employee 3
record17 assign to employee 4
record18 assign to employee 5
record19 assign to employee 6
record20 assign to employee 7
record21 assign to employee 1
record22 assign to employee 2
record23 assign to employee 3
record24 assign to employee 4
record25 assign to employee 5
record26 assign to employee 6
record27 assign to employee 7
record28 assign to employee 1

[bcmarshall]

Bob, I substituted the name of the query containing my data for "T1" in your example and got an error stating there were "Too Few Parameters. Expected 6"

I'm not quite sure what that means.

Orange, your example works for me but the problem is that I still have to somehow relate "Record 14" to a specific record in my set. That's why I was hoping for a query. It could be integrated in the recordset and would provide an extremely simple means of sorting.

[Bob Fitz]

Bob, I substituted the name of the query containing my data for "T1" in your example and got an error stating there were "Too Few Parameters. Expected 6"

I'm not quite sure what that means.

.......

The code I offerd in my earlier post was to be used instead of using a query.
Take a look at the attached db.
Table T1 has 2 fields: ID which is a PK and Num which is a field to hold the value of 1 - 7
Open table Ti to verify that all Num fields are currently 0. Close the table.
Open Form1 and click the button which will run the code in the module to change the values in the Num field.
Open the table again to verify the changes.
For you convienience, I have included an update query, which, when run, will return the value of Num to 0 in all records.

[bcmarshall]

Right at the bottom of this page is a list of "Similar Threads", and included in them is the following.

• Assign cases for employees to work
  
  By chrisernst in forum Access
  
  Replies: 2 Last Post: 06-18-2015, 06:51 AM
  
  The author provides a means to count all the records in the list. All I did was use a Mod expression against that count column and my problem is solved! Thanks to all of you.
  
  
  

[bcmarshall]

Bob, the prior solution is very similar to yours. I went ahead and implemented the other fix simply because it was done, but I greatly appreciate all your thought and effort and will give you a star.

Orange, I feel the same way. I found something that works for me and went with it but I have given you a star as well.

Thank you both for your effort.

[Bob Fitz]

Bob, the prior solution is very similar to yours. I went ahead and implemented the other fix simply because it was done, but I greatly appreciate all your thought and effort and will give you a star.

Orange, I feel the same way. I found something that works for me and went with it but I have given you a star as well.

Thank you both for your effort.

Thank you for your kind words and appreciation.

[orange]

@bcmarshall
Glad you have a solution. Thanks for the comment.
Adding a field to the table and populating it for sorting was the concept.

[bcmarshall]

Bob, I'm looking over what you offered and I have a question. If I substitute your T1 table that has two fields with my live data table with 20 fields, I'm trying to see how it would work. The update query starts by updating all fields to 0. What would I do in the case of a query returning live data or a table based on live data? Would I create a special field for this purpose? Your solution is far more elegant!

[bcmarshall]

I've been continuing my search of the internet, looking for that easy way to sequentially number records within a query. Once I have that there are dozens of things I might use it for, including this.

I found an incredibly straightforward solution to the sequential numbering, and I wanted to share it with anyone while thanking those who went out of their way to help.

Here's a link to the solution, which requires only 10 lines of code!

[link https://access-excel.tips/add-auto-n...-access-query/[/link]

I hope others find it as useful as I did!

[Welshgasman]

Link does not work?

[CJ_London]

link has been copied twice - try https://access-excel.tips/add-auto-n...-access-query/