OK, I will see what I can do tomorrow.
OK, I will see what I can do tomorrow.
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.
I will have to look at this tomorrow. I really appreciate all your help!!
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?
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?
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....
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.
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.
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.
OK.
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!
Well done.
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.
I will keep the C & R in mind - and yes it is extremely important - so back-ups for sure!
Posting Permissions
Reply With Quote
