Assign cases for employees to work

[chrisernst]

Hello,


We use an access database to track credit bureau disputes. Each day we receive an excel report each day with new customers disputing information with includes their account number, name, etc. Is there any way I can import an excel file and have the cases automatically assigned to 4 different users and have some kind of queue list display as to who has what? I want to make sure that the work is even spread to each employee

I do have some understanding of access with developing queries and macros in access itself. However, have limited knowledge with writing code for VBA or SQL.

I would appreciate any guidance and feedback.

[orange]

Have table for IncomingIssues and a table for YourUSER/Investigators and a table of IssueAssignedTo?


IncomingIssues-->IssueAssignedTo<----Users/Investigators

Do incomingissues have similar complexity? Some take more time than others?

If they are similar, Assign first issue to first investigator, 2nd to 2nd... just rotate through your list of investigators.
Start with #1 again when you get to issue #5.

If the issues are more complex, you'll have to set up some sort of weighting factor. If some issues can oly be handled by 1 or 2 users, then you need some sort of method to make such assignments.

Good luck.

[ranman256]

you would save the xl file to the same working file everytime..say Disputes.xls
link this into access as a linked table
build a query to append the data to the proper data table.
put this query in a macro.

So the actions would be:
1. save the latest xl file to Disputes
2. run import macro.

Now part 2., Assign the records where Employee = null on the table
Myself, I would have a form, that shows a list of employees. The list knows how many and can (divide recs where Employee = null) / lstEmp.count
now for some vb code...could be a bit much tho..

here it assumes the linked excel data table is called xlDisputes

the query "qsTopNrecs" pulls the top N records that have no employee assigned

the query "quAssignCases2Emp" is an update query that uses the item in the list

box on the form and assigns that person to the data.

'-----------------
Private Sub btnAssign_Click()
'-----------------
Dim i As Integer, iCount As Integer, iCasesPerEmp As Integer
Dim db As Database
Dim sQry As String
Dim qdf As QueryDef


sQry = "qsTopNrecs"   'top n recs in table not assigned ,where emp=null


iCount = DCount("*", "xlDisputes")   'get the import rec count
iCasesPerEmp = iCount / lstemps.ListCount  'cases / employee
   
   'SET QUERY WITH THE # RECORDS THAT CAN BE ASSIGNED
Set db = CurrentDb
Set qdf = db.QueryDefs(sQry)
qdf.SQL = "SELECT TOP " & iCasesPerEmp & " State, Emp FROM tData where ([emp] is 


null);"
qdf.Close


   'assign each employee some data
For i = 0 To lstemps.ListCount - 1
   lstEmp = lstemps.ItemData(i)
   DoCmd.OpenQuery "quAssignCases2Emp"
Next


   'show any leftovers
DoCmd.OpenQuery sQry


Set db = Nothing
Set qdf = Nothing
End Sub