A Secondary Query?

[blondie41]

I have an access database that is used to assign cases based on certain attributes and workload capacity of individuals.

As a case is assigned to a worker, their capacity is reduced by one. (I have figured out how to do this).

However, there are some variables that I can't figure out and am looking for some advice.

So to simplify...
We have two groups of "workers"
Group 1 has a primary attribute of "A" and a secondary attribute of "B"
Group 2 has a primary attibute of "B" and a secondary attribute of "A"

My goal is to assign all of the "A" cases to Group 1 until each of them reaches 0 capacity. Then I want to start assigning the cases to Group 2 (based on their secondary attribute of "A").

In addition (worst case scenario) if both Group 1 and Group 2 have no more capacity, any remaining cases would be assigned to Group 1.

Any suggestions on how to accomplish this? I was hoping to do it in a select query, but realize that may not be the best place. Any help is very much appreciated!

 

[kjv1611]

I realize this is already a little old, but it's the first time I've looked at it.

How do you currently have this setup?

I think you should have the table structure as this:
1. Employee Table (or contractor or whatever) with an ID, lastname, firstname, etc, PrimaryGroup, SecondaryGroup, MaxLoad field possibly here as well.
2. TaskType Table with a TaskTypeID and description - Or maybe this would be called your Categories, or Groups, however you want to word it.
3. TaskList - this would store an AutoNumber TaskID, Description, Type/Category/Group, a start date (maybe), and a complete date (definitely), and empID, definitely.

Then you'd build your queries to see/assign diff things. So for instance:
1. One query to list out how many tasks each user is currently responsible for (open tasks) of each category.
2. Another query to say, based on the first, how many more tasks they can take on before moving to their secondary category or else being totally finished.

Oh, and what about a ranking of the employees for each type? Do you also need a way to assign a certain individual before another? Not sure that you'd want to do that in all job situations, but there may be some instances where that would be best. If you could have an empRating perhaps, numeric, that lists out a weighted score of some sort, and then first fill jobs with their ID, and then move on to the next... And at the same time, to prevent burn-out of the top employee(s), you may want to also have it only fill one for the top employee, then 1 for next, and so on, until all get one, then go back around... To accomplish this, it might also be helpful to add a "date assigned" field to the task list.

Then as to finding out how many open spots each employee/worker has, you could have a query then that looks at the task table, comparing against the employees table, and pulls in the open tasks for each employee and the maxload from the employee table. Then if you also want to see all open tasks before assigning, you could query for all tasks with no USERID and No CompleteDate.

Anyway, that's just some thoughts on the topic. If you're still working on this, and check back here, let us know how it's going, and whether you need help going down this road at all.




"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57