Loop through tables updating field in 1 from field in other (1 Viewer)

Peg · Feb 6, 2015

I need a bit of help. I have 2 tables, "Counselors" and "Appeals". There are several names in the Counselor table but it is not a fixed number of names (rows) usually around 7. There is also some variable number of records in the Appeals table, could be 0, could be 50, depending on the day.

I need to, in a round robin fashion, assign the counselor names one by one to the records in the Appeals table. I can't quite get my head around how to loop through the 2 tables and update the name field in the Appeals table with the name in the Counselor table.

Any help would be much appreciated. Thanks!
Peg

jdraw · Feb 6, 2015

I think this logic should get you what you need (untested).

Code:

Counselors
 1,2,3,4
 Appeals
 1,2,3,4,5,6,7,8,9
 
 Round robin assignment of Appeals to Counselor
 Do This looping construct until all Appeals have been assigned.
 
 Open recordset of Appeals
 If recordset Appeals is EOF Then Quit
 Again:
  Open recordset of Counselors
  If recordset Counselors is EOF Then GoTO Again
 DO_IT:
 create record Assigned with CounselorId, AppealID
 get next counselor
 get next Appeal
 goto DO_IT
 end

 Should result in
 
 assigned
 C1A1,C2A2,C3A3,C4A4,C1A5,C2A6,C3A7,C4A8,C1A9

Loop through tables updating field in 1 from field in other (1 Viewer)

Peg

New member

jdraw

Super Moderator

Similar threads

Users who are viewing this thread