473,320 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Job rotation list based on qualification

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
7 2797
nico5038
3,080 Expert 2GB
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
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
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, 107 views)
Jun 29 '08 #4
nico5038
3,080 Expert 2GB
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
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, 102 views)
Jun 29 '08 #6
nico5038
3,080 Expert 2GB
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
3,080 Expert 2GB
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

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

Similar topics

21
by: M. Clift | last post by:
Hi All, Could someone help me out with this? items = ('a', 'b', 'c', 'd') items + 1 = ( 'b', 'c', 'd', 'a') items + 2 = ( 'c', 'd', 'a', 'b') items + 3 = ( 'd', 'a', 'b', 'c') trans = 1
33
by: Shawn B. | last post by:
Greetings, I am simulating an assembly language bit rotation in C# and it works wonderfully --------- .... public uint Value; .... public uint RotateRight(byte count) {
5
by: Russell Warren | last post by:
Does anyone have an easier/faster/better way of popping from the middle of a deque than this? class mydeque(deque): def popmiddle(self, pos): self.rotate(-pos) ret = self.popleft()...
0
by: VorTechS | last post by:
I'm having a problem with an inherited label, applying text rotation to aligned text. If text rotation is applied to the aligned text, the alignment goes 'nuts'. I can find no logic to what is...
2
by: Roger Li | last post by:
I got a problem about the mean rotation calculation. I have a group of rotation matrics in my program and I want to calculate the mean rotation on the basis of these rotation matrics. I think it...
6
by: Ramtin Kazemi | last post by:
Hi How can i perform bitwise rotation in C#?
6
by: elrondrules | last post by:
I want to create a simple log rotation for the following scenario: I have a script that when executed will write debug and error messages to a specific file (say script.log). I want to create...
6
by: woodey2002 | last post by:
Hi Everyone. Thanks for your time. I am trying to create a search form that will allow users to select criteria from multiple multi select boxes. So far i have managed to achieve a search option...
1
by: woodey2002 | last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just can’t get any where on. My databse mostly includes...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.