By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,469 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Job rotation list based on qualification

P: 4
Hi, i am working on a database, which are making job rotation on factory on diffrent task.

We have 25 people totaly at work
14 of them are working at the same time on 14 diffrent tasks.
Not all can do all 14 jobs/tasks.

Any ideas on how to make a randomized list, which exludes some tasks for the people who can not do the tasks.

Greatfull for all ideas and responses
Jun 28 '08 #1
Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
For getting a random list you can use a query with an additional random field like:

select person, rnd(12) as Random from tblPerson order by rnd(12);

This will sort the rows and every time the rows will be differently ordered.

The exclusion of persons from tasks is something you'll need to crate another table for with e.g. PersonID and TaskAllowed.

Enough for a start ?

Nic;o)
Jun 28 '08 #2

P: 4
For getting a random list you can use a query with an additional random field like:

select person, rnd(12) as Random from tblPerson order by rnd(12);

This will sort the rows and every time the rows will be differently ordered.

The exclusion of persons from tasks is something you'll need to crate another table for with e.g. PersonID and TaskAllowed.

Enough for a start ?

Nic;o)
Thnx alot,

Got the query, on randomize the people working.

Now i am realy stuck on the exckusion list.

I have set up a table on, People at work, then i have made a table Peoplework_id and true/false on each taskallowed.

The second real problem is to get it to show 14 people at work doing 14 diffrent task which they are qualified to do.

Would be greatful for help.
Jun 29 '08 #3

P: 4
For getting a random list you can use a query with an additional random field like:

select person, rnd(12) as Random from tblPerson order by rnd(12);

This will sort the rows and every time the rows will be differently ordered.

The exclusion of persons from tasks is something you'll need to crate another table for with e.g. PersonID and TaskAllowed.

Enough for a start ?

Nic;o)
I found out after using following code.
Expand|Select|Wrap|Line Numbers
  1. SELECT Navn, Int((14-1+1)*Rnd()+1) AS Random
  2. FROM Personell
  3. ORDER BY Int((14-1+1)*Rnd()+1); 
I have put out the database so fare
Attached Files
File Type: zip task.zip (45.5 KB, 73 views)
Jun 29 '08 #4

nico5038
Expert 2.5K+
P: 3,072
As stated: "The exclusion of persons from tasks is something you'll need to create another table for with e.g. PersonID and TaskAllowed."

In your Personell table you have the tasks "not normalized", this will require a UNION query to get the separate tasks.
Besides "splitting" the tasks you have the personell-id constraint.

This would require for each task a query to get one person and exclude a selected person from the further selection. This gives "task" queries like:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 Personell.[Personell-ID], Personell.[Arm 2], Rnd([personell-id]) AS Rand
  2. FROM Personell
  3. WHERE (((Personell.[Arm 2])=True))
  4. ORDER BY 3;
  5.  
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 Personell.[Personell-ID], Personell.[Arm 4], Rnd([personell-id]) AS Rand
  2. FROM Personell
  3. WHERE 
  4. (((Personell.[Personell-ID]) Not In (select [Personell-ID] from qryArm2)) 
  5. AND ((Personell.[Arm 4])=True))
  6. ORDER BY 3;
  7.  
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 Personell.[Personell-ID], Personell.[Arm 5], Rnd([personell-id]) AS Rand
  2. FROM Personell
  3. WHERE 
  4. (((Personell.[Personell-ID]) Not In (select [Personell-ID] from qryArm2) 
  5. And (Personell.[Personell-ID]) Not In (select [Personell-ID] from qryArm4)) 
  6. AND ((Personell.[Arm 5])=True))
  7. ORDER BY 3;
  8.  
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 Personell.[Personell-ID], Personell.[Arm 5], Rnd([personell-id]) AS Rand
  2. FROM Personell
  3. WHERE 
  4. (((Personell.[Personell-ID]) Not In (select [Personell-ID] from qryArm2) 
  5. And (Personell.[Personell-ID]) Not In (select [Personell-ID] from qryArm4)) 
  6. AND ((Personell.[Arm 5])=True))
  7. ORDER BY 3;
  8.  
Etc. I guess you ge the idea.
Finally a UNION query like:
Expand|Select|Wrap|Line Numbers
  1. select * from qryArm2
  2. UNION
  3. select * from qryArm4
  4. ...
  5.  
will give the final result.

There's however always the possibility that for the last employees nothing is found when the "early" tasks are already "locking" the employees allowed for a task. Best to start with the tasks with the fewest employees and use the "common" tasks as last in the creation of the "task queries".
So to use the final query a check or 14 (presently 13 as employee 14 has no tasks...) is needed and the query will have possibly executed several times before succeeding...

Nic;o)
P.S. Are you from Norway?
Jun 29 '08 #5

P: 4
As stated: "The exclusion of persons from tasks is something you'll need to create another table for with e.g. PersonID and TaskAllowed."

In your Personell table you have the tasks "not normalized", this will require a UNION query to get the separate tasks.
Besides "splitting" the tasks you have the personell-id constraint.

This would require for each task a query to get one person and exclude a selected person from the further selection. This gives "task" queries like:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 Personell.[Personell-ID], Personell.[Arm 2], Rnd([personell-id]) AS Rand
  2. FROM Personell
  3. WHERE (((Personell.[Arm 2])=True))
  4. ORDER BY 3;
  5.  
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 Personell.[Personell-ID], Personell.[Arm 4], Rnd([personell-id]) AS Rand
  2. FROM Personell
  3. WHERE 
  4. (((Personell.[Personell-ID]) Not In (select [Personell-ID] from qryArm2)) 
  5. AND ((Personell.[Arm 4])=True))
  6. ORDER BY 3;
  7.  
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 Personell.[Personell-ID], Personell.[Arm 5], Rnd([personell-id]) AS Rand
  2. FROM Personell
  3. WHERE 
  4. (((Personell.[Personell-ID]) Not In (select [Personell-ID] from qryArm2) 
  5. And (Personell.[Personell-ID]) Not In (select [Personell-ID] from qryArm4)) 
  6. AND ((Personell.[Arm 5])=True))
  7. ORDER BY 3;
  8.  
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 Personell.[Personell-ID], Personell.[Arm 5], Rnd([personell-id]) AS Rand
  2. FROM Personell
  3. WHERE 
  4. (((Personell.[Personell-ID]) Not In (select [Personell-ID] from qryArm2) 
  5. And (Personell.[Personell-ID]) Not In (select [Personell-ID] from qryArm4)) 
  6. AND ((Personell.[Arm 5])=True))
  7. ORDER BY 3;
  8.  
Etc. I guess you ge the idea.
Finally a UNION query like:
Expand|Select|Wrap|Line Numbers
  1. select * from qryArm2
  2. UNION
  3. select * from qryArm4
  4. ...
  5.  
will give the final result.

There's however always the possibility that for the last employees nothing is found when the "early" tasks are already "locking" the employees allowed for a task. Best to start with the tasks with the fewest employees and use the "common" tasks as last in the creation of the "task queries".
So to use the final query a check or 14 (presently 13 as employee 14 has no tasks...) is needed and the query will have possibly executed several times before succeeding...

Nic;o)
P.S. Are you from Norway?
Hi,
Yes i am from Norway.

Ok, so far so good, but the big problems seems to be, the conection of info.

Because i can't see to get the personel, at work and task to work togeter.

The output should be like this.

Name, 06-07, 07-09, 09-11
Hanne Arm2, Arm4, Taste,
Randi Arm4, Arm2, Face
And so on

I can't see to get this right.

Sorry to ask so much.
Attached Files
File Type: zip task1.zip (78.0 KB, 67 views)
Jun 29 '08 #6

nico5038
Expert 2.5K+
P: 3,072
The above query will result in a number of random assignments of the people to the tasks.
Your sample however looks like a combination of the possibilities based on your tblPersonell.
This would imply that you want to create all possible combinations of the 14 tasks assigned to the persons on forehand.
There's however a catch, as the allowed tasks can limit the work for certain employees. Just imagine two persons can do task 1 and 2, this will "block" these tasks for the other persons as these two persons will "rotate" for these tasks "eternally"...

Will give it a thought tomorrow to give all combinations, but also keep in mind that a change of tasks for the personell will also require a "rebuild" of these possibilities.

Nic;o)
Jun 29 '08 #7

nico5038
Expert 2.5K+
P: 3,072
To get all combinations a slightly changed version of the above query is needed.
I created a test table like:
PersonellID Task
1 a
1 b
1 c
2 a
2 b
3 a
3 c
and used the query:
Expand|Select|Wrap|Line Numbers
  1. SELECT a.PersonellID, a.Task, b.PersonellID, b.Task, tblPersonell.PersonellID, tblPersonell.Task
  2. FROM tblPersonell AS a, tblPersonell AS b, tblPersonell AS c
  3. WHERE (((b.PersonellID)>[a].[personellid]) AND ((b.Task)<>[a].[task]) AND
  4. ((tblPersonell.PersonellID)>[a].[personellid] And (tblPersonell.PersonellID)>[b].[personellid]) AND
  5. ((c.Task)<>[a].[task] And (c.Task)<>[b].[task]));
  6.  
to get:
a.PersonellID a.Task b.PersonellID b.Task tblPersonell.PersonellID tblPersonell.Task
1 c 2 b 3 a
1 b 2 a 3 c
1 a 2 b 3 c

The query works by selecting tasks that are unique by the WHERE Task <> Previously assigned tasks and makes sure that all employees are only visible once by the WHERE PersonelID > Previously found.
You'll have to elaborate the query to create a column for each person, but I guess the "pattern" is clear.

Nic;o)
Jun 30 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.