Combined Table Repeating values

[RedWolf3x]

I'm hoping this turns out to be easier than I'm making it.

I have two Tables CSRs and Monitors. There are significantly more CSRs than there are monitors. I would like to create a query where the CSRs are listed and the monitors are repeated to the end of the list.


CSRs
John


Mike
Joe
Steve
Susie
Kelly
Caryn
Candi
Cindy
Sarah

Monitors
Karen
Michael
Jenny

The Results would look like
CSRs Monitors
John Karen
Mike Michael
Joe Jenny
Steve Karen
Susie Michael
Kelly Jenny
Caryn Karen
Candi Michael
Cindy Jenny
Sarah Karen

Please keep in mind that the list of CSRs and the list of Monitors will be forever changing and I need to make the alterations as simple as possible. (No 1 to 1 relations between the CSR and the Monitor), Also to make things a little more difficult I'd like the names to alter with each passing month. (The monitors names would move up one row and another name would go at the bottom).

Ive just started this portion of the project and do not have any code to start with, but I've made some attempts that have so far failed miserably. Any help is greatly appreciated. Thanks.

Red

[June7]

I don't see how any query can accomplish this. Will require VBA code to assign Monitors to CSRs. Use SQL SELECT to open recordsets in VBA along with nested looping structures. That part is not really difficult as an exercise in programming logic. The tricky part is automating the rotation of Monitors each month. I suggest a field in Monitors table called Rotation that will store the sequence for writing to the CSRs table. Update these values after each monthly run. Use this field as sort order in recordset.

The procedure would be a Sub that writes the Monitor names to the CSRs table.

Attempt code and post for analysis when you encounter issue.