updating table based on criteria order
I want to automatically assign task orders to my team based on a certain criteria. I have 6 types of product. Not all people can do all types and some are better at 1 type them another.
I want to give everyone the case they "prefer" if available. I have a table of newwork and I have a table of employeeID and their preference 1p to 6P.
So far, I can take the first person and the first task order and see if they match, if they do then that person gets that task and if they don't then it moves on to the next task until it finds one that matches. The problem is when there is no task that matches the first preference and getting it to then check the 2nd preference (if there is one).
So if I have newwork
task# producttype
111 6
222 5
333 6
444 1
555 3
And I have EmployeeID and preference
EmployeeID 1p 2p 3p 4p 5p 6p
AA11 6 2 4
BB22 2 1 5 6
CC33 1 6
DD44 5 3
I am thinking I will need to run through the taskorders based in first preference (1P) and then somehow capture who didn't get work during that 1st pass so I can check to see if there is anything available for their 2nd or 3rd or 4th ect preference. so that it would look like this:
task# employeeID
111 AA11
222 DD44
444 CC33
333 BB22
555 DD44
I am using windows 10 and Access 2016. This might not be the best way to do what I want but this is what I thought of and this the code I have so far
Code:
Public Function newcs()
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim rst3 As Recordset
Dim per As Integer
Set rst1 = CurrentDb.OpenRecordset("newwork")
Set rst2 = CurrentDb.OpenRecordset("pref")
Set rst3 = CurrentDb.OpenRecordset("assignedwork")
rst1.MoveFirst
rst2.MoveFirst
per = rst2("1p").Value
Do Until rst1.EOF
If per = rst1("prodtype").Value Then
rst3.AddNew
rst3.Fields("taskID").Value = rst1.Fields("taskID").Value
rst3.Fields("employeeID").Value = rst2.Fields("employeeID").Value
rst3.Update
rst1.Delete
rst1.MoveFirst
rst2.MoveNext
Else: rst1.MoveNext
If rst1.EOF Then
If Not rst2.EOF Then
rst2.MoveNext
rst1.MoveFirst
End If
End If
End If
Loop
If rst1.EOF = rst1.BOF Then End
End Function
Right now there is nothing to move to the next preference type and there is no check to see if we are out of preference either.
