Rotating Job Schedule

[brharrii]

I have two tables. the first is a table of names, the second is a table of jobs that need to be done. I need to develop a job list that assigns a job to each person and then rotates the jobs every month so that everyone gets a new job each month. there are 7 people and 7 jobs. Each person has exactly one job and always has one job.

I'm inclined to think that a query is the answer here, but I'm not sure how to approach it. If anyone has any suggestions on a good way to go about developing something like this, it would be greatly appreciated.

Thank you

 

[JHB]

How many month do you need to plan forward?
You do have to take care of that a person not get the same job in the afterwards 7 month.
Does it also have to be random, so that person no. 1 not always come to the next job after person no. 2?
I don't think you can preform that only with a query, I think you have to write some function to get it mixed.

 

[brharrii]

In my mind, the rotation should be able to go on forever. It should not be random. the two lists should stay in the same order with one of them shifting each job up or down a level each month so that everyone gets a new job each month for 7 months until month 8 when the job assignments will start over again the same as they were in the first month.

I was able to achieve this in excel by adding the record ID to current month and year:

[Column B]=People ID number (1-7)
[Column C]=People Names
[Column E]=[Column B]+MONTH(NOW())+YEAR(NOW())

and then dividing by the highest possible multiple of 7 that didn't give a 0 answer:

[Column F]=IF(MOD([Column E],7)=0,7,MOD([Column E],7))

and then using the value in column F as the indicator in a Vlookup field:

[Column H]=VLOOKUP([Column F],Sheet2![Job ID]:[Job Description],2,FALSE)

it works brilliantly in Excel. The problem is that some of the formulas don't translate well to Access.