updating table based on criteria order

**MRummell** · 01-20-2019, 07:22 AM

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

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.

**OBP** · 01-21-2019, 03:04 AM

I can see your problem there is also the issue that you are biasing the selection to the first in the list ie if you have more than one person that has 6 as their first preference the 1st in the list will always get it.
Which leaves you open to accusations of favouritism. I am not sure how you overcome that without keeping a record of the previous selections made, perhaps having a date field in the newwork table would work.
You also do not have an indicator of whether or not a person's preference has been awarded to ignore it on the second or third runs. The date field may also help with that or have a field to flag when their work has been allocated.
You also have the issue that some may not get work at all if their are not enough jobs to go around, they should then be first in the queue at the next allocation of jobs.
Another issue is the layout of the preferences they should technically be individual records in a sub table rather than fields in one table, although it will still work.
So back to your original problem,you could just repeat the loop with more loops until there are no jobs left or everyone has a job.

**MRummell** · 01-21-2019, 06:50 AM

Yes, its a bit of a problem - thank you for taking time to look at this. MY initial thought was to just go through each person until they get work - but then someone who preferred a cetain task might not get it if the person before then had it as thier 2nd or 3rd preference and there were only a few of them. And yes, not everyone might get work that day (there is always tomorrow) and they should be on the list to get work first the next day so the same people aren't getting all the work. I date table might be the way to go...

How would I go about putting the preferences in a sub table? I thought maybe having then in their own table wasn't the best way to go about it - but wasn't sure what else to do.... I have the production types in a separate table and the employee info in another table and I just created a 3rd table to track which employee wanted (or knew how to do) which product type and in what order by pulling the info from these 2 tables -so I am not repeating the types or the employee info.

Yeah - I kinda got lost with my DO until Loop when I tried to get it to do more than 2 loops - I guess I got confused what was doing what when and it got stuck on someone who had a preference for a task we didn't have that day and it just kept loop through the work indefinitely. I tried to a for next loop to cycle through the preferences until it found one that matched the task type - but I couldn't do that while still maintaining my place in the work queue as the IF loop was kind of in the middle of the for next loop and Access didn't like that.

**OBP** · 01-21-2019, 07:38 AM

For the Preferences table you would have an EmployeeID and a producttypeID, each record would hold an employee ID and the product type ID from the product table, this links them together. You could also add an order field so that you could list all the first and then second and then third choices etc.
Unfortunately I can't work with Access 2010, only 2007 and previous versions otherwise I could create the VBA for you.
If you could manage to save the database as a 2007 version and put some dummy data in it I can certainly try it for you.
I am an old fashoined BASIC programmer so I tend to use For/Next loops based on the recordset count.
I think to make this work the newwork table will definitely need an indicator that shows that a job has been taken.

**MRummell** · 01-21-2019, 08:24 AM

OK - the preference table would have repeat employee IDs and 1 producttype field and then an order field to keep track of the preference that way... I already have them linked via id numbers in the preference table - I just went across instead of down - so I had 1 EmployeeID with many types instead of repeating the employeeID for each type they wanted. If I did it your way - then I could sort by thier preference order and just go down the list starting with preference order 1 - but then everyone would get their 2nd preference after getting 1 first reference even if there are more 1st preferences left to assign out.

What I was doing is taking the new work table and delete the record when it was assigned - it was added to the assigned work table - so I go through all the record until the new work table was empty and then I knew I got out all the new work. I could just append the new work into the assigned table and sort by records that don't have employee ID's yet - but then I would have to re-query after each assignment to get a new list of yet to be assigned work.

I get confused when the Loops get too nested, but I know that is what is needed here -to go through all the records. I think since I am currently deleting the record from the new work table, this might mess up the for/next idea...
I could add a yes/no field to the new work table and instead update it to yes instead of deleting it - then just delete the data in that table once they were all assigned out. I might be creating extra work for the database doing it this way. I thought it would be better to work with a smaller recordset then to try to filter the ever expanding assigned work table since it contains every task ever assigned and to who it was assigned.

**OBP** · 01-21-2019, 08:51 AM

The query can easily filter the non assigned jobs.
Or you can transfer them to an archive file, you never know when someone will ask about historical job usage.

ps if the employee preferences table had a date field you could update it with the date they are assigned a job, that could then be used to "order" the records for selcting the oldest dates first for a particular preference.

Do you want me to do some work on it?

**MRummell** · 01-22-2019, 05:44 AM

Yes please - I have attached a Dummy database. I too thought about adding the last date work was assigned as a way to track where to start - but with all the preferences being in the 1 table - I wasn't sure if it would update them all or just the one... I changed the preferences table to reflect an order number and only 1 preference for each row.Database2.zip

The old code is still in there though for going through the records and adding to the assigned work instead of appending the new work first and then updating it with the employee...

**OBP** · 01-22-2019, 06:43 AM

Hi, OK I have a copy of the database and can operate with it.
I am not sure how much I will get done today as I am off to my Poker Night at the local Pub at 7:00pm, so hopefully I can get it done tomorrow.

**OBP** · 01-22-2019, 06:48 AM

I have a question, what happens if you have a job on a product when all those employees that prefer that product have been allocated one already?

**MRummell** · 01-22-2019, 10:27 AM

They can get more than 1 job at a time - i just wanted to make sure everyone who could get one did first. (Someone might not get one if we don't have any of the type they do, or there were 2 people who can do the one type but we only have 1 job at the moment)

Anything you do to help will be greatly appreciated!

**OBP** · 01-22-2019, 10:30 AM

I think this does what you want, if not I will take another look tomorrow.

ps off to the pub now.

**MRummell** · 01-22-2019, 11:59 AM

What does the key find column do? It looks like it is only updating the date if the production type was assigned out... So when it starts again - it would start with someone's 2nd or 3rd preference because it doesn't think that person got any work before - but they did - just with their 1st preference.... So - can it update all preferences for anyone assigned work that day once it is done assigning out the work? I don't need employee AA11 to get a 2nd preference first the next day if their are additional 1st preferences available the next day.

So is this logic right? It takes the first job and scrolls through the employee's until the 1st preference matches the prodtype and then assigns it...? is it re-querying based on the assignment order after each time it assigns the work?

I can't follow the progression - the first 3 jobs seemed to be fine - no one who had not received work had a 1st preference for the job type that was listed first - so it assigned out 2nd preferences... but then when it got the 4th job - 1567890123 - it sent that one to CC22 - but both AA22 and AB22 had that as their 1st preference and they should have been closer to the top than CC22 which is who it was assigned to... it might be that the order changes with each requery - but the ones who still didn't get any work should still be first and I don't know why it would change who came first.....

When I cleared at the dates and started to assign like no one ever received work before - i was able to follow - but then we had the problem once everyone had a job and we still had tasks left - when it re-queries - it then keeps going to the first person on the list for that type and they get everything... So if we have a bunch of product type 4 - so everyone doing 4's would get more than 1 - when it requeries - it keeps stopping on the first person in the list to get 4's and they get every job left for that type... not sure how to fix that - maybe a counter to see how many they already have? Maybe don't re-query every time but wait until end of file and just move next?

I really do appreciate the help with this!!

**OBP** · 01-23-2019, 05:59 AM

If you mean the new keyfield in the Assignedwork table I added that as the key field because you had the key set to TaskID with No Duplicates, but the Newwork table has 3 records for 1123456789, this stops the code with a duplicate key record error.

I obviously haven't understand your complete requirements for the order of assigning the work.
I didn't test it for more work than employees, so that is something new.
Can you go through the database that we both posted with the original data and list who should get which job, I will then see if I can match it.
I have changed the pref query to put the preference first, but then CC! gets all the number 5 jobs and I don't know if that is what you want.

**MRummell** · 01-23-2019, 09:16 AM

OK - that was my fault with the dummy data - there should be no duplicates of taskID. Also everyone should only have 1 1st preference, 1 2nd - so I messed up BC22 as well and order 1P for 6 should be order number 2

This is what I want to happen

TaskID ProdType EmployeeID
1234567890 5 CC11
1345678901 5 BB22
1456789012 5 CC11
1567890123 3 AA22
1678901234 3 CC22
1789012345 3 AB22
1890123456 6 BC11
1901234567 6 AA11
1012345678 6 BC11
1123456798 1 BB11
1123456789 2 AB11
1223456789 4 BC22
1334567885 4 BC22
1445678912 6 AA11
1552344581 6 BB11

**OBP** · 01-23-2019, 10:12 AM

OK, I will see what I can do and get back to you.

**OBP** · 01-23-2019, 10:59 AM

Question.
I am looking at the first 3 jobs all for number 5.
You have
1234567890 5 CC11
1345678901 5 BB22
1456789012 5 CC11
Why choose BB22's second choice and not AA22's second choice?
You then chose CC11 again and still not AA22.

**OBP** · 01-23-2019, 11:03 AM

deleted duplicate post.

**MRummell** · 01-23-2019, 11:23 AM

Because AA22 got a 1st preference where BB22 didn't get a 1st preference.

So I took the first employee ID in the list BB11 and looked for their 1st preference type - so they got 1123456798. Then the next person AA22 got 1567890123 as the 1st task ID of type 3. Then CC22. When I got to AB11 - then did not have a 1st preference available in the list so i skipped over them and went to CC11 until I got to BB22 - and I didn't have any type 3 jobs left so I skipped them and went on to AA11. Then I went back to AB11 and looked for the 2nd order (since they didn't get work yet) and that was 1123456789, and then I went to BB22 since they were the other one who didn't get anything yet - and that gave them 1245678901 as the 1st job of their 2nd preference - skipping over AA22 since they already had something.

I think that gave everyone at least one - so I went back to the 1st preference and started all over again. (of course now there were less jobs with a 1st preference so we were down to 2 and 3rd preferences).

**OBP** · 01-23-2019, 11:34 AM

OK, so you are working in the opposite direction to how I envisaged doing this, it means going through the Employees first and matching them to the job and not matching the job to the employee as I thought it would work.

I will have to completely rewrite the code to do it that way and then try and fine tune the 2nd & 3rd preferences.

But I still don't understand how you decide which order to process the Employees at the moment.

**MRummell** · 01-23-2019, 12:47 PM

Yeah, I was more concerned with everyone getting something if possible - as I know I could just cycle through the jobs until they were gone - so I would get those all out. I just wanted to make sure everyone got something if possible. (If there is someone who can only work type 2 and 4 and there are no 2 or 4's - then they won't get anything this time - so you were right that i would then want them to really be 1st the next time the work comes around.) For the order - I just used the query in the database that sorted by order and the last day they got work - to put the ones who didn't get work at the top - the problem came once everyone had the same date for work received - then it just re-queried and kept giving the some person what was left.

So if I have 10 type 3 jobs and 4 people whose first preference is type 3 - i would want to give those to them - unless there is someone who didn't get their 1st preference because we didn't have that job type -if their 2nd preference was type 3 - then they should get 1 of them because they didn't get anything yet. I am trying to make it even and not give 4 people 2-3 jobs when someone who could have done one of those jobs gets none. Make we need a job count to order by too? I want to give as many 1st preference jobs out as I can - but if we don't have any of your 1st preference, i want to make sure you get something as well.

If I go by job type - then I think it might be harder to see who got the job at what preference? I worked it out both way and cycling through the employees seem to give more 1st preferences to more people.

By EmployeeID By Task ID
TaskID ProdType EmployeeID Preference TaskID ProdType EmployeeID Preference
1234567890 5 CC11 1st 1234567890 5 CC11 1st
1345678901 5 BB22 2nd 1345678901 5 AA22 2nd
1456789012 5 CC11 1st 1456789012 5 BB22 2nd
1567890123 3 AA22 1st 1567890123 3 AA22 1st
1678901234 3 CC22 1st 1678901234 3 CC22 1st
1789012345 3 AB22 1st 1789012345 3 AB22 1st
1890123456 6 BC11 1st 1890123456 6 BC22 1st
1901234567 6 AA11 1st 1901234567 6 BC11 1st
1012345678 6 BC11 1st 1012345678 6 AA11 1st
1123456798 1 BB11 1st 1123456798 1 BB11 1st
1123456789 2 AB11 2nd 1123456789 2 AB11 2nd
1223456789 4 BC22 2nd 1223456789 4 AA11 2nd
1334567885 4 BC22 2nd 1334567885 4 AB22 2nd
1445678912 6 AA11 1st 1445678912 6 BC22 3rd
1552344581 6 BB11 3rd 1552344581 6 CC22 4th

employees with more than 1 job: 5 employees with more than 1 job: 5
# of 1st preferences filled: 10 # of 1st preferences filled: 8.

Sorry - it smushes the columns together.

Thread: updating table based on criteria order

Thread Tools

Display

updating table based on criteria order

Posting Permissions