Code to distribute list

[Zeeshan7]

I use following code to distribute accounts equally to employees from table "Assign" to table "Accounts".

The code update one by one 'EmpID' from table "Assign" to field ‘EmployeeID’ in table "Accounts".

My question is how to put Criteria in below code if there is another field e.g. 'billing_ID', in both tables? The code should match and assign accounts where 'billing' codes are same. Thanks

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Accounts")
    Set rs2 = db.OpenRecordset("Assign")
    
    rs1.MoveFirst
    rs2.MoveFirst
    Do Until rs1.EOF
  
  rs1.Edit
  rs1!EmployeeID = rs2!EmpID
  rs1.Update
  
  rs2.MoveNext
  If rs2.EOF Then
      rs2.MoveFirst
  End If
  rs1.MoveNext
    Loop
    
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
    
End Function

[ADezii]

If I understand you correctly:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb

Set rs1 = db.OpenRecordset("Accounts")
Set rs2 = db.OpenRecordset("Assign")
    
rs1.MoveFirst
rs2.MoveFirst

Do Until rs1.EOF
  rs1.Edit
    'Assign Accounts only if Billing Code is the same
    If rs1![Billing_ID] = rs2![Billing_ID] Then
      rs1!EmployeeID = rs2!EmpID
    End If
  rs1.Update
  
  rs2.MoveNext
  If rs2.EOF Then
    rs2.MoveFirst
  End If
  rs1.MoveNext
Loop
    
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing

Set db = Nothing

[Zeeshan7]

If I understand you correctly:

Function assignaccounts()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb

Set rs1 = db.OpenRecordset("Accounts")
Set rs2 = db.OpenRecordset("Assign")
    
rs1.MoveFirst
rs2.MoveFirst

Do Until rs1.EOF
  rs1.Edit
    'Assign Accounts only if Billing Code is the same
    If rs1![Billing_ID] = rs2![Billing_ID] Then
      rs1!EmployeeID = rs2!EmpID
    End If
  rs1.Update
  
  rs2.MoveNext
  If rs2.EOF Then
    rs2.MoveFirst
  End If
  rs1.MoveNext
Loop
    
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Function

It works fine if in table "Assign" there is only one billing_ID e.g. 5. But if I add billing_ID e.g. 10 in table "Assign", the code does not distribute ALL Emp_ID in the table "Accounts", and skip billing_ID 5 even.

[Zeeshan7]

Thank you in advance for your time and consideration.

[Zeeshan7]

Please let me further explain you that suppose we have 12 records in table Accounts, 6 records related to billing_ID 5 and remaining 6 related to billing_ID 10.

In table Assign, we have 4 records, 2 related to billing_ID 5 and other 2 related to billing_ID 10.

Now the code is supposed to update all EmployeeID from EmpID of table Assign considering billing_ID, but the case is different. It is only updating first 2 and last 2 records of each billing_IDs.

[ADezii]

Please let me further explain you that suppose we have 12 records in table Accounts, 6 records related to billing_ID 5 and remaining 6 related to billing_ID 10.

In table Assign, we have 4 records, 2 related to billing_ID 5 and other 2 related to billing_ID 10.

Now the code is supposed to update all EmployeeID from EmpID of table Assign considering billing_ID, but the case is different. It is only updating first 2 and last 2 records of each billing_IDs.

Let me try to take another view of this before I go on Vacation.

[ADezii]

Please let me further explain you that suppose we have 12 records in table Accounts, 6 records related to billing_ID 5 and remaining 6 related to billing_ID 10.

In table Assign, we have 4 records, 2 related to billing_ID 5 and other 2 related to billing_ID 10.

Now the code is supposed to update all EmployeeID from EmpID of table Assign considering billing_ID, but the case is different. It is only updating first 2 and last 2 records of each billing_IDs.

Try the following code and see if it produces the desired results:

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
 
Set db = CurrentDb
 
Set rs1 = db.OpenRecordset("Accounts")
Set rs2 = db.OpenRecordset("Assign")
    
rs1.MoveFirst
rs2.MoveFirst
 
Do While Not rs2.EOF
  Do While Not rs1.EOF
    If rs1![Billing_ID] = rs2![Billing_ID] Then
      rs1.Edit
        rs1![Employee_ID] = rs2![EmpID]
      rs1.Update
    End If
  Loop
  rs1.MoveFirst
  rs2.MoveNext
Loop
    
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

[Zeeshan7]

The code is now constantly running. Can you please re-check? Thanks

[ADezii]

The code is now constantly running. Can you please re-check? Thanks

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
 
Set db = CurrentDb
 
Set rs1 = db.OpenRecordset("Accounts")
Set rs2 = db.OpenRecordset("Assign")
    
rs1.MoveFirst
rs2.MoveFirst
 
Do While Not rs2.EOF
  Do While Not rs1.EOF
    If rs1![Billing_ID] = rs2![Billing_ID] Then
      rs1.Edit
        rs1![Employee_ID] = rs2![EmpID]
      rs1.Update
    End If
    rs1.MoveNext
  Loop
  rs1.MoveFirst
  rs2.MoveNext
Loop
    
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

[Zeeshan7]

Please let me give you specific example so that the code should work as requested:

Table Assign:
EmpID : 1, 2, 3, 4
billing_ID: 5, 7, 5, 7

(EmpID, 1 and 3 related to billing_ID 5 and EmpID, 2 and 4 related to billing_ID 7)

Table Accounts:
total records: 8.
first 4 records related to: billing_ID 5
last 4 records related to: billing_ID 7

Now the result/ distribution should be like this in table Accounts:

EmployeeID: 1, 3, 1, 3 (for billing_ID 5) and 2, 4, 2, 4 (for billing_ID 7).
(Equally distributed as per billing_ID) Hope it clarifies.

Thanks for all your help.

[nico5038]

Guess we're close, try:

Dim db As Database
Dim rsB As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
 
Set db = CurrentDb
 
Set rsB = db.OpenRecordset("select distinct Billing_ID from Accounts")

while not rsB.eof
  Set rs1 = db.OpenRecordset("select * from Accounts where Billing_ID =" & rsB!Billing_ID)
  Set rs2 = db.OpenRecordset("select * from Assign where Billing_ID =" & rsB!Billing_ID)
    
  rs1.MoveFirst
  rs2.MoveFirst
 
  Do While Not rs2.EOF
    Do While Not rs1.EOF
      rs1.Edit
      rs1![Employee_ID] = rs2![EmpID]
      rs1.Update
      rs1.MoveNext
    Loop
    rs1.MoveFirst
    rs2.MoveNext
  Loop
  rsB.movenext
Loop
  
rsB.Close
rs1.Close
rs2.Close
Set rsB = Nothing
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

This does assume that the Billing_ID is a number, else surrounding single quotes are needed !

Nic;o)

[Zeeshan7]

The result of your code is same as previously posted by ADezii

Code Result:
3, 3, 3, 3 for billing_ID 5 and 4, 4, 4, 4 for billing_ID 7,

Requirement:
It should be like 1, 3, 1, 3 for billing_ID 5 and 2, 4, 2, 4 for billing_ID 7 (as per my specific example data) because I need equal distribution of Accounts/ records through all given EmpID exist in table Assign.
Please help. Thanks

[FishVal]

Hello, gentlemen.

The solution is quite clear.
I would not write a ready code, but specify its features.

• The code should use separate cursor for each employee. This may be achieved using:
  • Separate recordset for each employee
  • Single recordset but separate bookmarks to remember employee related cursor position
  • Single recordset with separate values of PK field of [Accounts] table.
  • Entities listed above may be stored in array or collection.
• Obviously, to achieve cyclic iteration, employee related cursor should be moved to first record only when the last record has been reached
• [Accounts] iteration cycle should recognize situation when no relevant employee is available.

Kind regards,
Fish.

P.S. Alternative solution: both recordsets could be sequentialy filtered by available [Billing_ID] values and iterated as in the original code. A part of job here may be done with SQL.

[nico5038]

The result of your code is same as previously posted by ADezii

Code Result:
3, 3, 3, 3 for billing_ID 5 and 4, 4, 4, 4 for billing_ID 7,

Requirement:
It should be like 1, 3, 1, 3 for billing_ID 5 and 2, 4, 2, 4 for billing_ID 7 (as per my specific example data) because I need equal distribution of Accounts/ records through all given EmpID exist in table Assign.
Please help. Thanks

Dear Zeeshan7,

It's obvious that the WHILE rs1 should be switched by the rs2. Perhaps the rs1.movefirst or rs2.movefirst is disturbing the logic, it won't harm to experiment with this yourself to get the thought behind the code.
Just use F8 to single step through the code to see what's happening.

Nic;o)

[Zeeshan7]

I am trying but still stuck. Appreciate if you could kindly modify the code.

P.S: With reference to the code at the top of this post, it produces the required result without considering billing_ID.

Thanks for your support.