How do I update all records in table 1 by looping through table 2?

[DWolff]

My application is to re-assign leads to different groups of salespeople by sequentially assigning them to each salesperson. I've got an Access 2000 front end to an MS-SQL database.

Currently, I do this effectively (but sloppily and slowly) by exporting Access records to Excel, doing my assignments there, importing back into a new work table in Access, and running an update query (joining on Lead.ID). However, I'm soon going to exceed the 64K record limitation in Excel. I'd like to accomplish the whole thing more eloquently in Access.

Let's call Table 1 LEADS, and Table 2 SALESPEOPLE.

I need to update the assigned Salesperson in the field LEADS.SP by sequencing through all records in SALESPEOPLE and looping until all selected LEADS are updated.

I used to do this in FoxPro or Dbase by opening the two tables and using syntax including "while not <eof>", "next", and "loop".

Is there a simple way to do this? I don't write VB code nor have I ever created Access Modules (though I can frequently look at existing code and modify it).

Thanks in advance for anyone's help!

[NeoPa]

You don't say, and I can't work out from going through the question, what you want to assign to the field LEADS.SP.
Is it a single salesperson to every lead in the table? Is it some sort of random balancing algorithm?

[DWolff]

Sorry it's been so long for me to notice your reply. . . .

The field Leads.SP is a salesperson name. I want to sequentially select each salesperson from the TABLE Salespeople, assigning the first record to the first lead, second record to the second lead, etc. When I finish sequencing through all the records in the TABLE Salespeople, it should start again from record 1 and continue sequencing until all leads are assigned.

[NeoPa]

You will probably struggle if you don't like coding :(
What you need (I'm a SQL man mainly but can't see a way of doing this in SQL I'm afraid) is to process through the two recordsets (SALESPEOPLE & LEADS) and assign one each as you go through.
I don't know where your assignments are made (they should be in a separate table).
An example of some basic recordset processing code can be found in Basic DAO recordset loop using two recordsets.
I hope this helps.