Report Random Selection Between 2 Tables

[charcurley]

I would like some help on a report I am trying to create: Here is what I would like to do:
I have a list of Employees.
and there is a list of Jobs to be Completed.
I want to Generate a Report that randomly matches an Employee with a Job. (there are about 10 jobs and 10 employees and over the next 10 weeks these jobs can not be repeated and we would like to run the report weekly or daily). I created an Employee table, a Job table and a table that keeps the job/employee match for future reference. Not sure I'm on the write track. Any help leading me in the right direction would be greatly appreciated!

 

[rubbernilly]

Let me see if I understand what you want to do...

an employee is assigned a random task every week, and they have a different task every week for ten weeks. Is that about it?

Well, it can be done pretty easily with a random assignment the first week... but after that, you would need a pretty sophisticated algorithm if every week you wanted the assignment to be random. The problem becomes that an Employee cannot have a task they have already had assigned to them, and they cannot have a task that has already been assigned that week. So there may come a week when a user has only 2 options left for the tasks they have not had over the past 8 weeks, but those 2 tasks were taken by employees already for this week.

So, let one random generation of tasks and one random generation of employee order suffice for every 10-week span. Then, roll the jobs through the emmployees, perhaps a random number of steps every week so that it appears to be even more random (so that employee A does not always follow Employee B in what job they do for the 10 week period).

Write the order to a table, and write it every week. Every week roll the value by one (take the Right() all but one, and add the Left() one on the end). If that table recordcount gets to be 10, then delete all entries, and regenerate the random order of tasks.

Your table would have three fields: JobOrder, EmpOrder, and Iteration.

For instance:

JobOrder      EmpOrder      Iteration
abcdefghij    1234567890    1

The next week, you get a random value for the iteration that has not already been used.

ghijabcdef 7890123456 5

So, the options are:

abcdefghij 1234567890 1
bcdefghija 2345678901 2
cdefghijab 3456789012 3
defghijabc 4567890123 4
efghijabcd 5678901234 5
fghijabcde 6789012345 6
ghijabcdef 7890123456 7
hijabcdefg 8901234567 8
ijabcdefgh 9012345678 9
jabcdefghi 0123456789 10

Of course, generating the employee order randomly might get you 8723610549 as a starting value. Then you would roll through that rather than 1234567890.

Pick your iteration randomly every week, and you can build your employee to task matchup based on your week 1 iteration.

HTH