Create query that will divide work among employees 1

[TheresAlwaysAWay]

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 10 people.

My thought was that if I could run a query that would assign the numbers 1-10 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 might offer.

After posting I realized that my simplification might get in the way. There could be 4 or 7 or any number of employees, so it couldn't be as simple as numbering all the records and only looking at the last character of the count.

 

[sxschech]

I did something similar by adding a Counter field to the table and then used vba to go through the recordset and update that field with the counter. So if you had 4 employees then it would put 1 2 3 4 and then repeat until end of the RS. If you can't modify the table, then you could have the recordset put the CompanyID and Counter in a separate table and join to that. You can then pass the counter to the vba code on a form or as a prompt if not using a form. You could also use the actual employeeid rather than 1 2 3 4 if you create a seconnd RS loop (Something like SELECT DISTINCT EMPID FROM TABLE [WHERE...]) then you don't need to worry about counting how many employees.

 

[TheresAlwaysAWay]

Thanks, sxschech. That sounds very close to the solution I'm seeking. Can you put that into a complete code structure?

 

[sxschech]

I'll see what I can come with. Are we doing a straight counter (1 2 3 1 2 3) or by employee?

 

[TheresAlwaysAWay]

Let's make it as easy as possible. Straight counter. I can put the username or employee identifier in if I have the rest working.

Seems the simplest way to do this is to just create a count field for all the records, and then use the Mod feature based on the number of employees to do the rest!

 

[TheresAlwaysAWay]

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!

[URL unfurl="true"]https://access-excel.tips/add-auto-number-in-access-query/[/url]

I hope others find it as useful as I did!

 

[sxschech]

Something like this. I copied some of my code and edited a bit and you will need to further modify for your table or queries, etc.
Need to add a field to your table for the counter.
You feed the sub the number at which you want to reset.

For example:
[tt]Call AddCounter(10)[/tt]
Would add 1 2 .. 10 and then start over until all the records are exhausted.

Message box is commented out, feel free to uncomment and edit to your message if you like, otherwise, it isn't necessary.

Sub AddCounter(NumberToUse As Integer)
'Create Recordset and then add a counter to each record
'When loop has reached the NumberToUse, reset the counter
    Dim db As DAO.Database
    Dim rs As DAO.Recordset    
    Dim i as integer
   
    Set db = CurrentDb 
    Set rs = db.OpenRecordset("SELECT * FROM TableQuery WHERE ... ORDER BY Customer")
   
    rs.MoveLast
    rs.MoveFirst
    statusbar ("Processing 1 of " & rs.RecordCount)
    i=0
    While Not rs.EOF                         
        If i <= NumberToUse then
		i=i+1
	else
	  	i=1
	end if		                                
	rs.Edit
        rs!CounterID = i
        rs.Update
        rs.MoveNext
        DoEvents
        statusbar ("Processing " & rs.AbsolutePosition + 1 & " of " & rs.RecordCount)
        DoEvents
    Wend
    statusbar ("Completed distribution list")
'    MsgBox "Counter list has been created.  Please review each email prior to sending.", vbOKOnly + vbInformation, "Distribute List"
    statusbar
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

 

[Andrzejek]

Let's say, you do divide the 100 customers among your 10 employees, initially. Over nest few days your employees take care of some customers, and some other customers are being still 'work on' (I don't know if they ever 'go away' and be done with?)

Then, a few days later, you get another 20 new customers. Do you, again, divide them up among your 10 employees. Or, you count how may customers each employee has and give the new 20 customers to the employees who have the smallest number of (remaining) customers?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson