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

Loop to update records

P: 1
HI,

I have two tables WORK and EMP.
Table work has fields - CaseNo,Date_Act, Handler.
Table Emp has one field - EmpName.

My field handler should be populated automatically from my table EMP.

Let us assume there are 100 records in work and 11 records in emp,
I need a query / Macro so that 100 Cases is distributed equally among 11 emp.
The challenge here is that my count of records for both the tables varies daily.

Please Help.
May 1 '10 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,492
So what have you tried so far? We don't simply provide solutions here. We help where you get stuck. That generally means you have to have attempted to build a solution.

I can tell you that a query is unlikely to be your best direction to pursue. I would expect this to be implemented via VBA coding.
May 2 '10 #2

P: 37
It won't be a problem to spread them evenly if you take for granted that new employees will have less cases assigned. You don't want them to get a burnout on the first week/month so it also comes in handy.

I think you would do with a 1 time full recordset loop, outer one being all the empty "WORK" handler fields, the the inner one all the employees who increment with +1 and rs.movefirst when their loop is done untill all handles are filled. It's there where you also need to expand loops or set criteria depending on your business logic (like max. nr of handles per person, how long cases can be open, etc)
May 2 '10 #3

Post your reply

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