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.

[nico5038]

Try:

function fncBilling()
 
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")
if rsB.eof then exit function ' No billings...
 
while not rsB.eof
  Set rs1 = db.OpenRecordset("select * from Accounts where Billing_ID =" & rsB!Billing_ID)
if rs1.eof then exit function ' No employees
  Set rs2 = db.OpenRecordset("select * from Assign where Billing_ID =" & rsB!Billing_ID)
if rs2.eof then exit function ' No billing data
 
  rs1.MoveFirst
  rs2.MoveFirst
 
  Do While Not rs2.EOF 'Loop through billing
    rs2.Edit
    rs2![EmpID] = rs1![Employee_ID]
    rs2.Update
    rs1.MoveNext
   ' Test no more employees, if so start again
    IF rs1.eof then
       rs1.movefirst
   endif
    rs2.MoveNext
  Loop
  rsB.movenext
Loop
 
rsB.Close
rs1.Close
rs2.Close
Set rsB = Nothing
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Function

Nic;o)

[FishVal]

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.

Sorry, Nico.

Didn't pay attention that you've already suggested the same.

As for the last code:

Line #10 IMHO, [Billing_ID] values should be retrieved from [Assign]
Line #15, #17 Depending on what table is used as [Billing_ID] values source, criteria in one line will always be False, and the second line should force next rsB iteration instead of function exit.

Regards,
Fish

[Zeeshan7]

I tried but it is not updating EmployeeID in accounts table. With reference to the code at the top, it produces the required result but without considering billing_ID. Can you look at it before writing code to produce the desired result? thanks

[nico5038]

Looks to me that the billing needs the employee ID.
I assumed that there's just one employee in the accont tale and many bills that need an employee to take care of.
Just switch the SET rs1 and SET rs2 when my assumption is wrong.

Nic;o)

[Zeeshan7]

there are many employeeIDs. each is assigned multiple, similar or distinct billing Ids. In accounts table each billing ID has dollar field sorted in descending order. If you look at the first code posted at the top of this post, the distribution of accounts to the employees was on the basis of dollar only. Now I want the distribution to be based on billing Id. Appreciate your efforts in solving this problem. regards.

[NeoPa]

I tried but it is not updating EmployeeID in accounts table. With reference to the code at the top, it produces the required result but without considering billing_ID. Can you look at it before writing code to produce the desired result? thanks

What is going on here? And who do you think you are, demanding code be written for you in such a specific way (or even at all)?

This is not a code writing service. We provide help and assistance for you to understand and learn the principles. WE help YOU code. Is that clear?

See post #13 for a template for how you should continue. If you have any specific difficulties with this then please post explaining clearly what you've tried and where you're having difficulty.

Otherwise, further demands for code will result in an official site warning. Not something you want on your record.

Administrator.

[Zeeshan7]

I am sorry. I will try to learn... thanks to all who answered my queries. please give me one more chance. I will wait for a reply of ADezii on this to conclude.
best regards.

[Stewart Ross]

Zeeshan7, it is not going to help to wait for a post from ADezii here. If there was more to say I think it would be along the lines of 'you need to redesign your application'. I have read your posts and can make little sense of your requirements to distribute billing IDs the way you say; perhaps you know what you mean but are not telling us. In any event, SQL-based databases have no defined internal order. Ordering can be done after the data is stored in any way that is appropriate. To say you need billing ID distributed in a way that relies on some form of intrinsic ordering that is not on the given field - for employeee IDs 1, 3, 1, 3 and so on - makes no sense to me at all, unless there is a date or something that you have not told us about to differentiate the occurrences of the same IDs.

You have not at any time explained what the billing IDs represent. I have read and re-read your post and have no idea.

I would suggest you reconsider what it is you wish us to help you with, take into account NeoPa's guidance on what is and is not acceptable, then re-post a much clearer account of what you are asking us to assist you with in a new thread.

We all enjoy helping others; but to do so we need you to help us as we neither know your application nor have your database in front of us at the time.

Regards

-Stewart

[Zeeshan7]

I have attached screen shot (txt file) to explain my requirement clearly.

Thanks and regards

[ADezii]

I am sorry. I will try to learn... thanks to all who answered my queries. please give me one more chance. I will wait for a reply of ADezii on this to conclude.
best regards.

Zeeshan7, to be perfectly honest with you, I would not wait for a reply from me since I have basically hit a stone wall with this one, and am currently on vacation. It was I who requested assistance on this Thread from five of the brightest minds, and most respected gentlemen that this, or any other Forum, has to offer. I am specifically referring to NeoPa, Nico5038, FishVal, missinglinq, and Stewart Ross Inverness. They graciously took the time from their busy schedules in order to assist you in arriving at a resolution to your problem. Frankly, I'm still confused as to exactly what you are looking for, thus the request for 'fresh eyes'. BTW, thanks gang!

[Zeeshan7]

I would request experts to kindly review my last post. I have attached a txt to clearly explain my requirment. Please accept my apology. Regards

[ADezii]

I would request experts to kindly review my last post. I have attached a txt to clearly explain my requirment. Please accept my apology. Regards

Dim MyDB As DAO.Database
Dim rstUniqueBillingIDs As DAO.Recordset
Dim rstAssign As DAO.Recordset
Dim rstAccounts As DAO.Recordset
Dim strSQL_1 As String
Dim strSQL_2 As String
Dim strSQL_3 As String
Dim intNumOfIDs As Integer
 
strSQL_1 = "SELECT DISTINCT Assign.Billing_ID FROM Assign ORDER BY Assign.Billing_ID;"
 
Set MyDB = CurrentDb
Set rstUniqueBillingIDs = MyDB.OpenRecordset(strSQL_1, dbOpenForwardOnly)
 
With rstUniqueBillingIDs
  Do While Not .EOF
    'Number of Accounts with stated [Billing_ID]
    intNumOfIDs = DCount("*", "Accounts", "[Billing_ID] = " & ![Billing_ID])
    'MsgBox ![Billing_ID] & " ==> " & intNumOfIDs
      Select Case intNumOfIDs
        Case 0      'No Accounts with [Billing_ID], do nothing
        Case 1      '1 Account with [Billing_ID]'Assign to the 1st Employee in the Assign Table
          CurrentDb.Execute "Update Accounts Set Employee_ID = " & DLookup("[EmpID]", "Assign", "[Billing_ID] = " & _
                             ![Billing_ID]) & " Where Accounts.[Billing_ID] = " & ![Billing_ID]
        Case Else   '> 1 Account for the [Billing_ID]
          strSQL_2 = "Select * From Assign Where Assign.[Billing_ID] = " & ![Billing_ID]
          strSQL_3 = "Select * From Accounts Where Accounts.[Billing_ID] = " & ![Billing_ID]
          Set rstAssign = MyDB.OpenRecordset(strSQL_2, dbOpenSnapshot)
          Set rstAccounts = MyDB.OpenRecordset(strSQL_3, dbOpenDynaset)
            Do Until rstAccounts.EOF
              rstAccounts.Edit
                rstAccounts![Employee_ID] = rstAssign![EmpID]
              rstAccounts.Update
 
              rstAssign.MoveNext
              If rstAssign.EOF Then
                rstAssign.MoveFirst
              End If
              rstAccounts.MoveNext
            Loop
      End Select
    .MoveNext
  Loop
End With
 
rstAccounts.Close
Set rstAccounts = Nothing
rstAssign.Close
Set rstAssign = Nothing
rstUniqueBillingIDs.Close
Set rstUniqueBillingIDs = Nothing

[Zeeshan7]

its done. thanks a lot.

[ADezii]

its done. thanks a lot.

You are quite welcome.