Need to loop through records set, update field based on another table

[jallen919]

I have one table containing sales records (with a column for AccountExec). One table containing list of Account Executives (with yes/no column to designate active status).

Need to loop through the sales table and assign one active Account Exec to each record. Want it to loop through list of Account Execs, one at a time so sales records are assigned evenly.

Not quite sure how to code that or even if that is the best way to evenly update the table with account execs.

Any suggestions or help are much appreciated.

 

[BoxHead]

Is this a one-time assignment or will it be updated as Account Executives move from Active to Inactive?

If it's one-time, I'd Filter the Account Executive Table by "Active", Copy the Filtered Names Column, Paste Special > Text into an Excel spreadsheet and Fill Down until I had the same number of rows in excel as Sales Records in my Table. (Note: after you paste, you'll want to delete Row1 because it will contain your column heading)

Then, Copy from excel and Paste into the AccountExec column in your Sales Records Table.


If the assignments are to be updated as AEs move from Active to Inactive, you could run into some problems with a Loop that assigns them consecutively. Let's say that seven of ten AEs are Active and you assign them to 700 accounts this way. They each have 100 accounts they are assigned to.

AE1 is assigned to accounts 1,8,15,22,29,36,43...
AE2 is assigned to accounts 2,9,16,23,30,37,44...
AE3 is Inactive
AE4 is assigned to accounts 3,10,17,24,31,38,45...
AE5 is assigned to accounts 4,11,18,25,32,39,46...
So on and so forth.

A month later, AE3 becomes active. Now instead of 100 accounts each, the 8 active AEs have 87 or 88 accounts.

They are 88 different accounts.

AE1 is now assigned to accounts 1,9,17,25,33,41,49...

If you've ever dealt with a company where you can never talk to the same salesperson twice, you know what a big deal that is.

I think you would be better off creating a new table of 'virtual' account executives. Call them VAE1, VAE2, VAE3, etc. Permanently assign the customers to these virtual AEs and then link your Active AEs (real people) to the VAEs (job assignments).

Also, create many more virtual AEs than you really need. I'm thinking 5 to 10 times as many depending on the size of the sales record table. Link a real AE name to five or more virtual AE IDs.

This way, when 'John Jones' who is linked to VAE6, VAE7, VAE8, VAE9, & VAE10 goes on Family Medical Leave, the accounts that are assigned to the Virtual AEs can be temporarily reassigned to five active AEs and even out the workload.

I hope this makes sense.

Boxhead

 

[MichaelRed]

I'll refer to your ttables as:

sales records: SaleRec

Account Executives : AcctExec

Make a record set for AcctExec (rstAcctExec)
Makw a recordset for the SaleRec(rstSaleRec) {Only the Active Ones}

While Not rstSaleRec.Eof
[tab]If (rstAcctExec.Eof) Then
[tab][tab]rstAcctExec.MoveFirst
[tab]End If
[tab]rstSaleRec.AcctRep = rstAcctExec.AcctRep
[tab]rstAcctExec.MoveNext
[tab]rstSaleRec.MoveNext
Wend

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[THoey]

Good answer Michael. I was thinking of something like that (mentally pseudo coding) and wanted to make sure whoever posted the reply took into account the problem of more accounts than reps. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.