Here at CSTAT we have had 130-150 open client cases at a time for at least the last 2 years. Our clients fill out a consulting request form on our website, which is then e-mailed to our office staff. A few years ago, I build us a custom Access database that the staff enter each request into, along with effort data from consultants' timesheets. Our Operations Database has a bunch of custom forms, queries, and reports that allow us to manage all sorts of data about each request and automate various processes. As cases get entered, they each get a unique CaseID for tracking purposes.
For example, we have a query & report that show us a sorted list of client requests that are waiting in our backlog (they need to be assigned to a consultant). I can run that report on demand to look at our brief descriptions of the nature of each backlog case (excerpted from how the client described the problem). Typically, our Assistant Director and I review that list when assigning cases to consultants, trying to match clients needs to consultants' skills. We also have to consider consultants' existing caseloads and recent history of weekly total effort when deciding who can take on more clients, so we have additional queries and reports that provide those pieces of information. Once we assign a case to a consultant, we forward the original e-mail to the consultant along with the CaseID. We record the assignment in our database (we have separate variables for the primary & optional secondary consultant on each case).
Our office staff send each consultant an electronic report each week that lists all open cases assigned to him or her, divided into sections for primary vs secondary consultant role. The list includes a few key pieces of info about each client case (current status, request date, assignment date, etc.), including our cumulative "case notes" about what has happened on the case. The consultants send back weekly electronic updates to case status and the case notes and weekly timesheets, which the office staff enter into the database.
Prior to our weekly staff meeting, I run and review a report that lists all open and recently closed cases (last 10 days). The most important thing I'm looking at in there is the statuses (what got closed, indicating we might be able to add a new case to someone's caseload) and the case notes. In the latter, I'm usually looking to see whether I should nudge the case toward closing because there's been little activity or the problem seems to be solved, it needs to be canceled or put in "on hold" status for a while, or indications that it might be an interesting case to discuss at the staff meeting. New notes often get appended after we discuss a case or make a decision during the staff meeting. I annotate the report with those and give it to our office staff to enter those updates into the Operations database.
I personally also add new notes about specific cases when I have unscheduled conversations or e-mails with consultants that warrant noting something. For example, we track every bid/cost estimate we provide to clients as sub-records attached to the respective case. We note the date, total, bid status, funder, submission & funding decision dates, etc. and use that later to create comprehensive summaries of our grant/proposal activity.
Access gives me a ton of options for designing reports & queries, so I usually design new ones that are specialized to support specific tasks that recur. This has helped enormously in scaling up over the last few years. Still, there's a lot of administrative oversight required as you scale up.
------------------------------
Steven Pierce
Associate Director
Center for Statistical Training and Consulting, Michigan State University
------------------------------