By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,116 Members | 1,319 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,116 IT Pros & Developers. It's quick & easy.

Code to distribute list

P: 44
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

Expand|Select|Wrap|Line Numbers
  1. Dim rs1 As DAO.Recordset
  2. Dim rs2 As DAO.Recordset
  3.     Set db = CurrentDb
  4.     Set rs1 = db.OpenRecordset("Accounts")
  5.     Set rs2 = db.OpenRecordset("Assign")
  6.  
  7.     rs1.MoveFirst
  8.     rs2.MoveFirst
  9.     Do Until rs1.EOF
  10.  
  11.   rs1.Edit
  12.   rs1!EmployeeID = rs2!EmpID
  13.   rs1.Update
  14.  
  15.   rs2.MoveNext
  16.   If rs2.EOF Then
  17.       rs2.MoveFirst
  18.   End If
  19.   rs1.MoveNext
  20.     Loop
  21.  
  22.     rs1.Close
  23.     rs2.Close
  24.     Set rs1 = Nothing
  25.     Set rs2 = Nothing
  26.     Set db = Nothing
  27.  
  28. End Function
Sep 6 '08 #1
Share this Question
Share on Google+
28 Replies


ADezii
Expert 5K+
P: 8,627
If I understand you correctly:
Expand|Select|Wrap|Line Numbers
  1. Dim rs1 As DAO.Recordset
  2. Dim rs2 As DAO.Recordset
  3.  
  4. Set db = CurrentDb
  5.  
  6. Set rs1 = db.OpenRecordset("Accounts")
  7. Set rs2 = db.OpenRecordset("Assign")
  8.  
  9. rs1.MoveFirst
  10. rs2.MoveFirst
  11.  
  12. Do Until rs1.EOF
  13.   rs1.Edit
  14.     'Assign Accounts only if Billing Code is the same
  15.     If rs1![Billing_ID] = rs2![Billing_ID] Then
  16.       rs1!EmployeeID = rs2!EmpID
  17.     End If
  18.   rs1.Update
  19.  
  20.   rs2.MoveNext
  21.   If rs2.EOF Then
  22.     rs2.MoveFirst
  23.   End If
  24.   rs1.MoveNext
  25. Loop
  26.  
  27. rs1.Close
  28. rs2.Close
  29. Set rs1 = Nothing
  30. Set rs2 = Nothing
  31.  
  32. Set db = Nothing
Sep 6 '08 #2

P: 44
If I understand you correctly:
Expand|Select|Wrap|Line Numbers
  1. Function assignaccounts()
  2. Dim db As Database
  3. Dim rs1 As DAO.Recordset
  4. Dim rs2 As DAO.Recordset
  5.  
  6. Set db = CurrentDb
  7.  
  8. Set rs1 = db.OpenRecordset("Accounts")
  9. Set rs2 = db.OpenRecordset("Assign")
  10.  
  11. rs1.MoveFirst
  12. rs2.MoveFirst
  13.  
  14. Do Until rs1.EOF
  15.   rs1.Edit
  16.     'Assign Accounts only if Billing Code is the same
  17.     If rs1![Billing_ID] = rs2![Billing_ID] Then
  18.       rs1!EmployeeID = rs2!EmpID
  19.     End If
  20.   rs1.Update
  21.  
  22.   rs2.MoveNext
  23.   If rs2.EOF Then
  24.     rs2.MoveFirst
  25.   End If
  26.   rs1.MoveNext
  27. Loop
  28.  
  29. rs1.Close
  30. rs2.Close
  31. Set rs1 = Nothing
  32. Set rs2 = Nothing
  33. Set db = Nothing
  34. End Function
  35.  
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.
Sep 12 '08 #3

P: 44
Thank you in advance for your time and consideration.
Sep 12 '08 #4

P: 44
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.
Sep 12 '08 #5

ADezii
Expert 5K+
P: 8,627
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.
Sep 12 '08 #6

ADezii
Expert 5K+
P: 8,627
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:
Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim rs1 As DAO.Recordset
  3. Dim rs2 As DAO.Recordset
  4.  
  5. Set db = CurrentDb
  6.  
  7. Set rs1 = db.OpenRecordset("Accounts")
  8. Set rs2 = db.OpenRecordset("Assign")
  9.  
  10. rs1.MoveFirst
  11. rs2.MoveFirst
  12.  
  13. Do While Not rs2.EOF
  14.   Do While Not rs1.EOF
  15.     If rs1![Billing_ID] = rs2![Billing_ID] Then
  16.       rs1.Edit
  17.         rs1![Employee_ID] = rs2![EmpID]
  18.       rs1.Update
  19.     End If
  20.   Loop
  21.   rs1.MoveFirst
  22.   rs2.MoveNext
  23. Loop
  24.  
  25. rs1.Close
  26. rs2.Close
  27. Set rs1 = Nothing
  28. Set rs2 = Nothing
  29. Set db = Nothing
Sep 12 '08 #7

P: 44
The code is now constantly running. Can you please re-check? Thanks
Sep 13 '08 #8

ADezii
Expert 5K+
P: 8,627
The code is now constantly running. Can you please re-check? Thanks
Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim rs1 As DAO.Recordset
  3. Dim rs2 As DAO.Recordset
  4.  
  5. Set db = CurrentDb
  6.  
  7. Set rs1 = db.OpenRecordset("Accounts")
  8. Set rs2 = db.OpenRecordset("Assign")
  9.  
  10. rs1.MoveFirst
  11. rs2.MoveFirst
  12.  
  13. Do While Not rs2.EOF
  14.   Do While Not rs1.EOF
  15.     If rs1![Billing_ID] = rs2![Billing_ID] Then
  16.       rs1.Edit
  17.         rs1![Employee_ID] = rs2![EmpID]
  18.       rs1.Update
  19.     End If
  20.     rs1.MoveNext
  21.   Loop
  22.   rs1.MoveFirst
  23.   rs2.MoveNext
  24. Loop
  25.  
  26. rs1.Close
  27. rs2.Close
  28. Set rs1 = Nothing
  29. Set rs2 = Nothing
  30. Set db = Nothing
Sep 13 '08 #9

P: 44
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.
Sep 13 '08 #10

nico5038
Expert 2.5K+
P: 3,072
Guess we're close, try:

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim rsB As DAO.Recordset
  3. Dim rs1 As DAO.Recordset
  4. Dim rs2 As DAO.Recordset
  5.  
  6. Set db = CurrentDb
  7.  
  8. Set rsB = db.OpenRecordset("select distinct Billing_ID from Accounts")
  9.  
  10. while not rsB.eof
  11.   Set rs1 = db.OpenRecordset("select * from Accounts where Billing_ID =" & rsB!Billing_ID)
  12.   Set rs2 = db.OpenRecordset("select * from Assign where Billing_ID =" & rsB!Billing_ID)
  13.  
  14.   rs1.MoveFirst
  15.   rs2.MoveFirst
  16.  
  17.   Do While Not rs2.EOF
  18.     Do While Not rs1.EOF
  19.       rs1.Edit
  20.       rs1![Employee_ID] = rs2![EmpID]
  21.       rs1.Update
  22.       rs1.MoveNext
  23.     Loop
  24.     rs1.MoveFirst
  25.     rs2.MoveNext
  26.   Loop
  27.   rsB.movenext
  28. Loop
  29.  
  30. rsB.Close
  31. rs1.Close
  32. rs2.Close
  33. Set rsB = Nothing
  34. Set rs1 = Nothing
  35. Set rs2 = Nothing
  36. Set db = Nothing
This does assume that the Billing_ID is a number, else surrounding single quotes are needed !

Nic;o)
Sep 14 '08 #11

P: 44
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
Sep 14 '08 #12

FishVal
Expert 2.5K+
P: 2,653
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.
Sep 14 '08 #13

nico5038
Expert 2.5K+
P: 3,072
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)
Sep 14 '08 #14

P: 44
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.
Sep 14 '08 #15

nico5038
Expert 2.5K+
P: 3,072
Try:

Expand|Select|Wrap|Line Numbers
  1. function fncBilling()
  2.  
  3. Dim db As Database
  4. Dim rsB As DAO.Recordset
  5. Dim rs1 As DAO.Recordset
  6. Dim rs2 As DAO.Recordset
  7.  
  8. Set db = CurrentDb
  9.  
  10. Set rsB = db.OpenRecordset("select distinct Billing_ID from Accounts")
  11. if rsB.eof then exit function ' No billings...
  12.  
  13. while not rsB.eof
  14.   Set rs1 = db.OpenRecordset("select * from Accounts where Billing_ID =" & rsB!Billing_ID)
  15. if rs1.eof then exit function ' No employees
  16.   Set rs2 = db.OpenRecordset("select * from Assign where Billing_ID =" & rsB!Billing_ID)
  17. if rs2.eof then exit function ' No billing data
  18.  
  19.   rs1.MoveFirst
  20.   rs2.MoveFirst
  21.  
  22.   Do While Not rs2.EOF 'Loop through billing
  23.     rs2.Edit
  24.     rs2![EmpID] = rs1![Employee_ID]
  25.     rs2.Update
  26.     rs1.MoveNext
  27.    ' Test no more employees, if so start again
  28.     IF rs1.eof then
  29.        rs1.movefirst
  30.    endif
  31.     rs2.MoveNext
  32.   Loop
  33.   rsB.movenext
  34. Loop
  35.  
  36. rsB.Close
  37. rs1.Close
  38. rs2.Close
  39. Set rsB = Nothing
  40. Set rs1 = Nothing
  41. Set rs2 = Nothing
  42. Set db = Nothing
  43. End Function
Nic;o)
Sep 14 '08 #16

FishVal
Expert 2.5K+
P: 2,653
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
Sep 14 '08 #17

P: 44
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
Sep 14 '08 #18

nico5038
Expert 2.5K+
P: 3,072
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)
Sep 14 '08 #19

P: 44
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.
Sep 14 '08 #20

NeoPa
Expert Mod 15k+
P: 31,429
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.
Sep 14 '08 #21

P: 44
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.
Sep 14 '08 #22

Expert Mod 2.5K+
P: 2,545
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
Sep 14 '08 #23

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

Thanks and regards
Attached Files
File Type: txt ScreenShot.txt (922 Bytes, 263 views)
Sep 14 '08 #24

ADezii
Expert 5K+
P: 8,627
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!
Sep 14 '08 #25

P: 44
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
Sep 15 '08 #26

ADezii
Expert 5K+
P: 8,627
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
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstUniqueBillingIDs As DAO.Recordset
  3. Dim rstAssign As DAO.Recordset
  4. Dim rstAccounts As DAO.Recordset
  5. Dim strSQL_1 As String
  6. Dim strSQL_2 As String
  7. Dim strSQL_3 As String
  8. Dim intNumOfIDs As Integer
  9.  
  10. strSQL_1 = "SELECT DISTINCT Assign.Billing_ID FROM Assign ORDER BY Assign.Billing_ID;"
  11.  
  12. Set MyDB = CurrentDb
  13. Set rstUniqueBillingIDs = MyDB.OpenRecordset(strSQL_1, dbOpenForwardOnly)
  14.  
  15. With rstUniqueBillingIDs
  16.   Do While Not .EOF
  17.     'Number of Accounts with stated [Billing_ID]
  18.     intNumOfIDs = DCount("*", "Accounts", "[Billing_ID] = " & ![Billing_ID])
  19.     'MsgBox ![Billing_ID] & " ==> " & intNumOfIDs
  20.       Select Case intNumOfIDs
  21.         Case 0      'No Accounts with [Billing_ID], do nothing
  22.         Case 1      '1 Account with [Billing_ID]'Assign to the 1st Employee in the Assign Table
  23.           CurrentDb.Execute "Update Accounts Set Employee_ID = " & DLookup("[EmpID]", "Assign", "[Billing_ID] = " & _
  24.                              ![Billing_ID]) & " Where Accounts.[Billing_ID] = " & ![Billing_ID]
  25.         Case Else   '> 1 Account for the [Billing_ID]
  26.           strSQL_2 = "Select * From Assign Where Assign.[Billing_ID] = " & ![Billing_ID]
  27.           strSQL_3 = "Select * From Accounts Where Accounts.[Billing_ID] = " & ![Billing_ID]
  28.           Set rstAssign = MyDB.OpenRecordset(strSQL_2, dbOpenSnapshot)
  29.           Set rstAccounts = MyDB.OpenRecordset(strSQL_3, dbOpenDynaset)
  30.             Do Until rstAccounts.EOF
  31.               rstAccounts.Edit
  32.                 rstAccounts![Employee_ID] = rstAssign![EmpID]
  33.               rstAccounts.Update
  34.  
  35.               rstAssign.MoveNext
  36.               If rstAssign.EOF Then
  37.                 rstAssign.MoveFirst
  38.               End If
  39.               rstAccounts.MoveNext
  40.             Loop
  41.       End Select
  42.     .MoveNext
  43.   Loop
  44. End With
  45.  
  46. rstAccounts.Close
  47. Set rstAccounts = Nothing
  48. rstAssign.Close
  49. Set rstAssign = Nothing
  50. rstUniqueBillingIDs.Close
  51. Set rstUniqueBillingIDs = Nothing
Sep 15 '08 #27

P: 44
its done. thanks a lot.
Sep 15 '08 #28

ADezii
Expert 5K+
P: 8,627
its done. thanks a lot.
You are quite welcome.
Sep 15 '08 #29

Post your reply

Sign in to post your reply or Sign up for a free account.