Dynamic Assignment of Accounts to Staff 1

[LevelThought]

Currently, have 2 tables as depicted below.
Ideally, I would like to "automatically" assign accounts for review by employee staff. Upon automatically assigning encounters for review, the date assigned field is populated with today's date and the status field is populated with "open."

MainTable
AcctID--AcctNo--Bal----DtLastPymt---#ofDaysSinceLastPymt--
Z22-----80670---1000---5/5/2006-------5
Z23-----80677---2000---2/2/2006------71
Z23-----80678---5000---12/1/2005----116
Z24-----80680----751---12/2/2005----115

EmpAssignTable
EmpName--AcctID----CurrentOpenProjects--ReviewRank---BalThreshold--DaysSinceLastPymt
Jill---Z22---4-----1-------1000------50
Jill---Z30---4-----2--------500------50
Jill---Z31---2-----3-------1000------60
Joan---Z23---3-----1-------1000-----150
Joan---Z24---4-----2--------750-----100
John---Z44---5-----1--------250-----200
John---Z46---3-----2-------2000-----300


The automatic assigning of encounters to staff for review should be weekly. In other words, at the beginning of the week, the number of projects assigned to a employee and open should not be less than 10. Currently, I assign manually-one account at a time using a form.
In the example above, Jill has 10 open projects and would not be assigned any new projects.

However, Joan only has 7 projects and therefore, need 3 additional projects assigned to her. So, the logic would be that according to the column titled "Review rank," accounts with an account id of "Z23" would be assigned first, then accounts with a account id of "Z24" would be assigned to Joan. Further, due to Joan's balance threshold (as displayed above in EmpAssignTable), she
should only review accounts on account id "Z23" that have a balance greater than or equal to $1000 and where there has not been a payment received within 150 days (See
DaysSinceLastPymtThreshold "150").
I think I can perform the assigment of accounts using an update query similar to "update MainTable, set AssignedTo equal to "Joan" where Sum(CurrentOpenProjects) is less than 10" and balance is greater than or equal to "1000." But, I want to automate the process because there
are over 50,000 accounts and each day, over 1000 accounts are appended to the Main Table.


My initial thought is that this would have to be a update query combined with several if statements within a CASE statement or maybe ADO coding with a counter?? Is this even feasible?

If so, any suggestions as to how I can accomplish? Thanks in advance!

 

[LevelThought]

Sorry for the confusion. Obviously, I need to rephrase the question.

I have 2 tables, MainTable and EmpAssign, as depicted below. I would like to "automatically" assign accounts to my employees whenever their "workload" is less than 10 accounts.

Upon automatically assigning encounters for review, the date assigned field is populated with today's date and the status field is populated with "open."

MainTable
AcctID--AcctNo--Bal----DtLastPymt---#ofDaysSinceLastPymt--
Z22-----80670---1000---5/5/2006-------5
Z23-----80677---2000---2/2/2006------71
Z23-----80678---5000---12/1/2005----116
Z24-----80680----751---12/2/2005----115

EmpAssignTable
EmpName--AcctID----OpenAccounts
Jill-----Z22-------4--------
Jill-----Z30-------4--------
Jill-----Z31-------2--------
Joan-----Z23-------3--------
Joan-----Z24-------4--------
John-----Z44-------5--------
John-----Z46-------3--------

Note, each employee has a list of account ids that he/she is responsible for as displayed in the Column titled "AcctID" in the EmpAssignTable.

In the example above, Jill has 10 "open" accounts and would not be assigned any new accounts.

However, Joan has 7 "open" accounts and therefore, need 3 additional accounts assigned to her.


Any ideas as to the structure of VBA that will "loop" through the Main table and assign 3 additional accounts to Joan? Also, there are two additional fields on the MainTable - "AssignedTo" and "DateAssigned" that will be populated upon the assignment of a account.

Thanks in advance!

 

[Golom]

I'm probably more dense than usual this morning but there seems to be a critical piece of information missing. I can identify which employees need to have accounts assigned with

Select EmpName, (10 - SUM(OpenAccounts)) As [Required Assignments]
From EmpAssignTable
Group By EmpName
HAVING SUM(OpenAccounts) < 10

It yields
[tt]
EmpName Required Assignments
Joan 3
John 2
[/tt]

MainTable lists individual AcctID and AcctNo fields. I infer from EmpAssignTable that an entry like
[tt]
EmpName AcctID OpenAccounts
Jill Z22 4
[/tt]
Means that "Jill" has been assigned 4 AcctNo accounts within AcctID "Z22". Is that correct?

... and is it also correct that MainTable also contains ALL accounts ... assigned or not?

Now (and this is what's missing), somewhere there should be a table of the form
[tt]
tblEmpAccounts
EmpName (PK)
AcctID (PK)
AcctNo (PK)
[/tt]
That shows which accounts have been assigned to an employee. Assuming that there is such a table then

Select M.AcctID, M.AcctNo

From MainTable M LEFT JOIN tblEmpAccounts E
     ON  M.AcctID  = E.AcctID
     AND M.AcctNo  = E.AcctNo

Where E.AcctID IS NULL

will give a list of all unassigned accounts.

Now using those queries and some code, we can assign accounts to employees
(Typed but not tested.)

Dim rsUnassigned   As DAO.Recordset
Dim rsRequired     As DAO.Recordset
Dim n              As Integer
Dim SQL            As String

Set rsRequired = CurrentDB.OpenRecordset ( _
    "Select EmpName, (10 - SUM(OpenAccounts)) As [Required Assignments] " & _
    "From EmpAssignTable " & _
    "Group By EmpName " & _
    "HAVING SUM(OpenAccounts) < 10 "

Do Until rsRequired.EOF

   Set rsUnassigned = CurrentDB.Openrecordset ( _
       "Select M.AcctID, M.AcctNo " & _
       "From MainTable M LEFT JOIN tblEmpAccounts E " & _
       "     ON  M.AcctID  = E.AcctID " & _
       "     AND M.AcctNo  = E.AcctNo " & _
       "Where E.AcctID IS NULL "
   
   n = 0
   Do Until rsUnassigned.EOF
	n = n + 1
      SQL = "INSERT INTO tblEmpAccounts (EmpName, AcctID. AcctNo) " & _
            "VALUES (' & rsRequired![EmpName] & "',' & rsUnassigned![AcctID] & "'," & rsUnassigned![AcctNo] & ")"
      CurrentDB.Execute SQL
      If n = rsRequired![Required Assignments] Then Exit Do
      rsUnAssigned.MoveNext
   Loop
   
   rsUnassigned.MoveNext

Loop

[COLOR=Black Cyan]' Now update EmpAssignTable[/color]
CurrentDB.Execute "Delete * From EmpAssignTable"
CurrentDB.Execute "INSERT INTO EmpAssignTable (EmpName,AcctID,OpenAccounts) " & _
                  "Select EmpName, AcctID, Count(*) " & _
                  "From   tblEmpAccounts " & _
                  "Group By EmpName, AcctID "

Some (or perhaps more than some) of that may not be to your liking for the following reasons
[li]Your EmpAssignTable contains what appear to be computed fields and, if that's really the case then keeping those computed values up to date becomes a problem. You should be doing computations in a query and not permanently storing the results in a table.[/li]
[li]Note that an employee who currently has no projects assigned will not get assignments because that employee does not appear in the EmpAssignTable. Perhaps there is a better source of who should be assigned projects.[/li]


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[LevelThought]

Golom,

Thanks for the insight.

I am currently reviewing the responses.

The "OpenAccount" field on the EmpAssignTable should be dynamically updated on a daily basis that provides the Manager with the number of accounts that each employee has "open" for each particular AcctID that the employee is responsible for. The employees do not have access to the EmpAssignTable, only the manager.

For your first question - Yes, that is correct. Jill has been assigned 4 accounts within AcctID "Z22." Note, this would show in the two additional fields on the MainTable, "AssignedTo" and "DateAssigned." I did not display the two additional fields in my revised posting. For example, the value of the "AssignedTo" field would be "Jill" for each of the accounts on the main table that have a AcctID of "Z22" that Jill has been assigned. Also, upon assigning the particular accounts, the "DateAssigned" would be automatically populated.

For the second question - Yes, that is correct. The MainTable contains all accounts (assigned or not) and is appended daily with over 1000 unassigned accounts!

In reference to missing data, wouldn't the two additional fields on the MainTable, "AssignedTo" and "DateAssigned" take care of this?

Also, the "DtLastPymt" and "#ofDaysSinceLastPymt" are calculated fields that have been stated as required fields by the Director even after I mentioned the "maintenance overhead." Therefore, I plan to use a passthru query to the Oracle database to update these fields on a daily basis. However, I believe that this part is a entirely new
post.

I will resume my review of the code that you provided.



Thanks in advance.

 

[LevelThought]

Finished review of the information.

Any additional comments considering that the "AssignedTo" and "DateAssigned" fields are on the MainTable?