Code to distribute list

[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.