updating table based on criteria order

[MRummell]

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]

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]

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]

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]

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]

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]

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.23606

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]

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]

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]

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]

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

ps off to the pub now.

[MRummell]

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]

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]

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]

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

[OBP]

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]

deleted duplicate post.

[MRummell]

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]

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]

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.

[OBP]

OK, I will see what I can do tomorrow.

[OBP]

Well that has been hard work.
But I have got it working after a fashion.
I have added an update query to reset the NewWork Assigned field.
I have also copied the Preferences Table so that it can be replaced with the original setup if required.
I will let you run the VBA code and see how much it does of what you want.

[MRummell]

I will have to look at this tomorrow. I really appreciate all your help!! :clap:

[MRummell]

This is really really close to what I need - however - right now it stops after a first pass through the preferences, and it doesn't skip an order preference if one has already been assigned a higher order....
If I kept it as is - then i just need to add so logic - that if recounter2 is not 0 then re-query the pref? (rst1) (if we have many TASKID's of the same type -say I have 20 task type 4's but this only gives each person who can do a type 4 - 1 job - and then it stops even though there are more type 4 jobs not assigned. (I guess i could just run the assign work again but it would be nice it assigned them all out at once) Is this what the copy of the preference table was for?

What if instead, I wanted to put the preference order in a temp table - say starting with everyones' order 1 - then it steps through each one - if it doesn't find a task ID/type for that employee's preference 1 then it would add that employee's preference 2 to the end of the table - so once it got through all the preference 1's it would start on the preference 2's that were added and if there was not a preference two - it would add a preference 3 ect, until there were no more preferences left and then it would go back to the beginning with preference 1. I would think it would be OK to still keep the logic to add another preference 2 to the end as we would either get all the jobs out before it reached that - or we would need that anyway.

Do you think that would work? There would need to be logic to move to the next order and to maybe skip adding if we reach the end of the preferences since not everyone has the same amount of preferences... In my mind this would work - but I might have the logic wrong... What do you think?

[OBP]

Regarding the stopping once all the Employees have been actioned when tasks are still available is why I ran it the other way using the tasks as th control.
So what we can do is use the Recordcounter for tasks and if it is not zero go back and run the whole thing again.
Do you think that would work?
Or you could as you say just duplicate the whole preferences table a few times to ensure that you enough preferences to cover the tasks, have you any idea how many is the most tasks you are likely to get?

[MRummell]

No - I could not say how many tasks we are likely to get - we are hoping to grow this - so we want to keep increasing it. I could add a do until EOF loop since the new work is always being re-queried - that should work? I would have to do a requery of the preferences at the end of the preference record count and then movefirst again - right?

I was trying to dynamically create the preference table but ran into problems with that too - I was thinking of taking the original preference list - as it has things in the order i want - mostly and then making a new table that started off with just the 1st preferences. Then if we got to the end of the record count for the newwork and nothing was assigned - it would go and get the next preference in order for that employee ID and append it to the new preference table.

I am having trouble getting it to append the next preference though - looks like I would have to do a simultaneous count of the current preference query and the new preference table and then append that record into the table when no work was assigned. (ugh!) That would make 3 counters for this program.

Question though - if i open a recordset at the begining and then append records to it as I go along - will Access 'see' those new records without having to requery the recordset? requerying might mess up the order i have for the preferences....

[OBP]

Won't just copying the Preferences currently in the table to double or treble the what is there work?
If you change the table dynamically you would definitely need to requery the recordset to pick up the additions.

[OBP]

I have something for you try, I have created a new field in the Preferences table called "phase" and set them all to 1 to order the current values for a first pass by setting phase in the pref query to the first sort field.
I have created an append query that copies the Copy of Preferences table to the preference table and sets the new records to phase = 2.
So it will now run through the 2 (or 3 if you wanted) phases until all the jobs are allocated.

[MRummell]

To answer the first question - I don't think that will do exactly what I want - but I will look at the phase setting and see. The issue is that if there is a 1st preference available, I want to use that and not just go on to the 2nd or 3rd preference. I only want to go to someone's 2nd or 3rd preference if there are no more 1st preferences available for them.

[OBP]

OK.

[MRummell]

So - I didn't go with the phases, instead a created another table to put the preferences into and updated that based an indexnum and the date last assigned. What it does is take the first employee and looks for the task that matches the 1st preference type - if it doesn't find it then it takes the preference query - finds the next order preference and add that to the table and delete the 1st preference (since we don't have any tasks of that type). If it does find a match then it updates the table with date and time and moves on to the next. Each time it requeries so the employee who never received work or had work before today would be on top - while still getting order preference 1 out first (due to the index number) if there was no date. It also remove the employeeID from the table if they didn't have any preferences that matched the tasks on hand.

It works exactly as I want it to -I just had to change the date last assigned to include the time and add a 1 second delete every time it updated the table - hopefully that won't be so bad in the long run.

I could have never done this without your help - thank you much! :bow:

[OBP]

Well done. :beerchug:
Don't forget to take a copy and then Compact & Repair the master database as it will have a lot of Design Bloat with so many changes.
There may also be some bloat from adding & deleting records so an occssional C & R would keep that under control as well.
If this database is fairly important I would also suggest a regular back-up copy.
I used VBA to take a dated daily copy which went in to a folder for the day ie Monday, Tuesday, Wednesday etc.

[MRummell]

I will keep the C & R in mind - and yes it is extremely important - so back-ups for sure!