Dividing records evenly at different levels

[treebark]

Hello,
I am trying to setup some process/code to evenly distribute records to a batch of employees:
I currently have 5 employees (that list will probably grow) and I have over 4000 records. I already have a way to divide them evenly between the employees, the problem is there is different levels of records. The records all belong to an entity, some entities have 1 record in the list, some have 5, the max is one entity with 25 records. The issue is trying to divide the records easily whilst keeping the records of the same entity assigned to the same employee. I tried assigning based on the entity, which obviously divides them evenly at the entity level (roughly 600 for each employee) but looking at the count of the records one user has around 800 while another has nearly 1000. The only idea I have is to use some kind of math statement that looks at the count of records a user has, I'm just not sure if it can be translated into code:
Employee 1 - assigned entity with most records (25)
Employee 2 - entity 2 (14)
Employee 3 - entity 3 (14)


Employee 4 - entity 4 (11)
Employee 5 - entity 5 (11)
Normally it would be Employee 1 again, but the next entity is 11 records, so I wanted to have the code look at the current number of records assigned to the next employee (Employee 1) and if the current assigned records + entity 6 (11) is greater than what Employee 2 has, skip Employee 1, and so on.

Any ideas or if this is possible?
Thank you for your help

[knarfreppep]

Try something like this -
1 count number of rows to be allocated (r)
2 count number of employees (e)
3 calculate number of rows per employee (n = r / e)
4 create a grouping query to return Entity and Number of Rows for Entity ordered by Number of Rows for Entity DESC
5 cycle through grouping query to -
a. test whether Employee #1 will have too many rows if these are added
b. if not then run an update query to update a new EmployeeID field in your base table to contain the ID of the Employee being allocated records
c. otherwise reset the count, get the next Employee, carry on
6 After this there may still be some unallocated rows i.e. with EmployeeID still empty ... just loop through Employees adding these records to them one at a time until all are allocated.

[treebark]

Below is the code I have assigning records evenly by entity, where and what would I insert to accomplish 5a?

Thanks

Function AssignJob()Dim db As Database
Dim rsJob As Recordset
Dim rsEmp As Recordset
Dim rsLastEmp As Recordset




Dim intLastEmpID As Integer
Dim strJob As String
Dim strEmp As String
Dim strLastEmp As String


Set db = CurrentDb


strJob = "select * from tblgci where assigned is null"
Set rsJob = db.OpenRecordset(strJob, dbOpenDynaset)




strEmp = "select * from qryActiveEmployee"
Set rsEmp = db.OpenRecordset(strEmp, dbOpenDynaset)


strLastEmp = "select * from tblgci where assigned is not null"
Set rsLastEmp = db.OpenRecordset(strLastEmp, dbOpenDynaset)


If rsJob.RecordCount = 0 Then
    MsgBox "No record to assign or all records aleardy assigned"
Else
    If rsJob.EOF Then Exit Function
        rsJob.MoveFirst
        With rsEmp
            If rsLastEmp.RecordCount = 0 Then
             rsEmp.MoveFirst
             intLastEmpID = rsEmp!EmpId
            Else
             rsLastEmp.MoveLast
             intLastEmpID = rsLastEmp!assigned
             rsEmp.FindFirst "EmpId = " & intLastEmpID
             rsEmp.MoveNext
             If rsEmp.EOF Then
                rsEmp.MoveFirst
             End If
            End If
            
        End With
    Do While Not rsJob.EOF
        rsJob.Edit
        rsJob!assigned = rsEmp!EmpId
        'rsjob!assigneddate = date'
        'rsjob!assignedby = User'
        rsJob.Update
        rsJob.MoveNext
        rsEmp.MoveNext
        If rsEmp.EOF Then
            rsEmp.MoveFirst
        End If
    Loop
  MsgBox "Assigned Records Successfully"
End If


  rsJob.Close
  rsEmp.Close
  rsLastEmp.Close
  db.Close
  
  Set rsJob = Nothing
  Set rsEmp = Nothing
  Set rsLastEmp = Nothing
  Set db = Nothing




End Function

[orange]

What exactly is the goal? If you have 4000 records and 5 employees, then your first "evenly distributed assignment" would be 800 records/employee.
If you have some "custom categories/subject areas" and you want to ensure the same category/subject related records go to a specific employee, then you need to know how many records by Category/Subject exist. I suggest you'll need to know which employee are associated/ can deal with which category(ies)/subject(s).

[treebark]

It is not so much categories as parent "entities":
100 Entities
4000 Records
Some Entities have uneven number of records compared to the others
All the records of each entity, no matter how many, have to been assigned to the same person ( Employee 1 gets Entity 1 that have 50 records, then Employee 2 gets Entity 2 that has 20 records, and so on)
I am trying balance as best as possible based on the record count but keeping in mind the entity restriction
Thank you

[ArviLaanemets]

1. Read the number of records for every entity p.e. by COUNT query and get a list of entities in DESCENDING order of rows numbers;
2. Read the number of employees (let's assume it is N) and create a list of employees in any order you prefer;
3. Assign to every employee in ASCENDING order of assigned entity rows (at start this will be 0 for all employees, so original ordering will be applied) one of topmost N undistributed entities in DESCENDING order of rows, and the number of rows of entity - and remove the entity from entity list, or mark it distributed somehow;
4. Reorder employees list by entity rows in ASCENDING order;
5. When there remains undistributed entities in entity list, go to p. 3, otherwise end.

Edit. When after some time you get next batch on entities to distribute, and there are some previous entities not closed for employee, you run the same procedure again - only you start p.3 with employee list ordered by remaining entities.

[knarfreppep]

This is an easy problem to solve with queries and running through a Recordset and certainly without needing to DIM anything more than the Recordset and a counter and without leaving any records unallocated for the 'next batch' ... one surely doesn't want to leave work that could be done unallocated to an Employee until some indeterminate future allocation run?

If it's a serious and important problem, let me know and I'll post a solution.

[treebark]

If it's a serious and important problem, let me know and I'll post a solution.

Please do, I cannot figure out a good code for it, I like to keep it simple.
Thank you

[knarfreppep]

Try this.

Create an populate 2 tables -
5 rows in table Employees with fields Employee_ID (Integer) and Name (Short Text)
4000+ records in table Tasks with fields ClientID (Integer), TaskDescription (Short Text), and Employee (Integer) ... create 1 row and then use an append query to add the rest exponentially.

Create module for code below and run the code.

Option Compare Database
Option Explicit

Private Sub AllocateTasks()

Dim TasksPerEmployee As Long
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim TasksAllocatedToEmployeeSoFar As Long

TasksPerEmployee = Int(DCount("ClientID", "Tasks") / DCount("Employee_ID", "Employees"))

DoCmd.SetWarnings False

Set rs1 = CodeDb.OpenRecordset( _
"SELECT Employee_ID FROM Employees", dbOpenSnapshot)
While Not rs1.EOF
TasksAllocatedToEmployeeSoFar = 0
Set rs2 = CodeDb.OpenRecordset( _
"SELECT ClientID, Count(ClientID) AS NumberOfTasks" & _
" FROM Tasks" & _
" WHERE Employee Is Null" & _
" GROUP BY ClientID", dbOpenSnapshot)
While Not rs2.EOF
If TasksAllocatedToEmployeeSoFar + rs2!NumberOfTasks > TasksPerEmployee Then
rs2.MoveLast
Else
DoCmd.RunSQL _
"UPDATE Tasks SET Employee = " & rs1!Employee_ID & _
" WHERE ClientID = " & rs2!ClientID
TasksAllocatedToEmployeeSoFar = TasksAllocatedToEmployeeSoFar + rs2!NumberOfTasks
End If
rs2.MoveNext
Wend
rs1.MoveNext
Wend

' At this point there may still be unallocated records
' I'll leave it to you to add to the code to allocate them

DoCmd.SetWarnings True

Set rs2 = Nothing
Set rs1 = Nothing

MsgBox "Done !"

End Sub

[treebark]

So great code and thanks, but this accomplishes what I originally was doing: assigned records evenly for each employee
I need to be able to assign evenly by the entity, which each entity has a varying number of records associated:
Entity 1 - 5 Records
Entity 2 - 20 Records
etc.

I have to assign each employee the most even numbers of records but they have to have all the records assigned to them for the entities that are assigned to them. I have even been able to assign by entity and sort by descending count of the records per entity and that gets me pretty even, but because some entities have only 1 record I could get it even more evenly distributed, but that is the part I am unaware of how to do: being able to evenly distribute records based on entity
Thank you

[aytee111]

This is a design issue, not an Access issue, so get the design right (on paper, in Excel, etc) before coming into Access.
5 employees
5 entities
entity1 has 50 records
entity2 has 20 records
entity3 has 25 records
entity4 has 3 records
entity5 has 2 records

What will each employee get?

[treebark]

This is a design issue, not an Access issue, so get the design right (on paper, in Excel, etc) before coming into Access.
5 employees
5 entities
entity1 has 50 records
entity2 has 20 records
entity3 has 25 records
entity4 has 3 records
entity5 has 2 records

What will each employee get?

Well this example doesn't necessarily work, because I realized I can assign the entities with multiple records first and then come up with some math to evenly distribute the remaining entities that have only 1 record:
"if employee 2 has less count/sum of records than employee 1, assign next entity
End if employee 2 is equal in count/sum of records to employee 1"
Repeat for each Employee, this could be the end all function I need to translate to code

[aytee111]

Next step is to write in English/pseudo code, again outside of Access. Such as "read first entity record, read first employee record, assign entity to employee, read next entity record,......" and keep going to the end. Then change the data and make sure your logic still works. Once your logic works, it can be written in VBA.

[orange]

I agree with aytee111 -- get the logic/algorithm down on paper and try it with test data. It's really an old and tested approach -- a prototype, a test,..... a blueprint.

If you determine the count of Entity * Records for that Entity, you will find some "logic" that will work for you.

My guess (and it's just a guess at this point) is to find these counts by Entity. And order by Descending Count.
Then, using a list of Employees (Emp1 ----Emp5)
Assign the first 5 to Emp1 thru 5, then for the next 5 assign them to Emp 5---Emp1.
For the next 5 assign to Emp1 to Emp5...

Good luck.

[ArviLaanemets]

The simpler (but not ideal) algorithm will be like in my previous post.

Create a list of entities (a table or an array) where you store entity ID and the number of rows for this entity. Order the list so, that at topmost one is the entity with most rows, then the one with nextmost number of rows, etc. At bottom of list will be entities with single row;
Add into another table or array employee ID's and entity ID's of top 5 entities from first table/array - 1 entity for every employee. Remove added entities from first table/array;
Into third table or array store the employee ID's and number of rows assigned to every employee. Order this table/array so, that the employee with smallest number of rows will be first, and the one with biggest number of rows will be last;
Assign next 5 entity ID's from 1st table/array into second one for employees in order estimated by 3rd table/array - i.e. the entity with most rows will be assigned to employee in 2nd table/array with smallest number of rows in 3rd table/array. Remove added entities from 1st table/array;
Add row numbers of entities added to second table/array to row numbers for matching employees in 3rd table/array. Reorder this table/array - again so, that the employee with smallest number of rows will be at top;
Continue this cycle until no entities are left in 1st table/array - in 2nd table/array will be the list of entities assigned to every employee.

This will be work OK, when the there are no extraordinary entities with many rows compared to other ones, and when there are no big gaps in distribution of row numbers between entities. But when there will be such anomalies, they will not be corrected.

The other, more complex solution will be.

Determine a percentage (p.e. 0.8 or 0.9) you want to use, and save it to some variable (var1);
Create a 1st table/array like in previous algorithm;
Add into 2nd table/array entiy ID's and row numbers of top 5 entities from first table/array, and order it by row numbers in descending order. Assign an employee ID to every row in 2nd table/array. Remove added entities from 1st table/array;
Save into variable the number of rows of topmost entity (var2). Into second variable save the value of 1st one multiplied by percentage (var3). Let's refer to them as upper and lower limit;
Cycle through remaining 4 employees in 2nd table/array, saving the number or rows for employee into variable (var4);
When the number of rows for assigned employee from 2nd table/array you are processing (var4) is more than var3, go to next employee
When the number of rows for assigned employee from 2nd table/array you are processing (var4) is less than var3, cycle through 1st table/array and save the current number of rows to variable (var5);
If var4 + var5 > var2, then continue to next record in 1st table/array;
If var4 + var5 <= var2, then add this entity to processed employee in 2nd table/array, and remove it from 1st one. And add the number or rows to var4. After that, check again var4 against var3. Repeat the cycle until var4 >= var3, or when you are at the end of 1st table/array;
After you have processed all employees in batch, add employee ID's and entity ID's from 2nd table/array into 3rd one (like 2nd one in simpler algorithm), and enter row info into 4th one (like 3rd one in simpler algorithm), and clear the 2nd table/array. After that you can read next 5 entities form 1st table/array into 2nd one and repeat the process - until all entities are distributed.