Loop to update records

[Prashant Pradeep]

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.

[NeoPa]

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.

[robjens]

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)