Creating a rotating Job list

[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

[June7]

Have job assignment field in names table. Store the job number in that field. Code to rotate assignments:

Sub Jobs()
Dim rs As ADODB.Recordset
Dim i As Integer
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Employees ORDER BY JobID DESC;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
rs!JobID = 1
rs.MoveNext
For i = 7 To 2 Step -1
rs!JobID = i
rs.MoveNext
Next
rs.Close
End Sub

[brharrii]

Thanks June, you're awesome

I'll play with this.

[brharrii]

I'm a bit rough with VBA, I put the code behind a form (is that correct?).

How do I display the current job assignments?

Thanks again.

[June7]

Goes wherever you want. I tested it from a general module. Can put it in a button Click event.