A Case Study: Starting with a Microsoft Sample Access Application

This is a “Customer Service” application. Here is the Case detail form.

assigned_to

This sample application takes a simple approach, and thus it shows clearly the difference between an application that records what has happened and one that tries to make use of data to anticipate what might happen.

These cases know who they were assigned to and when they were opened and when they were closed, but, when the user opens the drop down to assign an employee to a case is there some help we could provide if the application knew more? We can query to see how many open cases each employee has and add that information to the list of employees in the drop down.

employee_with_number_of_cases

Now new cases can be given to employees who do have fewer cases already assigned to them.

It could well be that not all cases are equal, some might take 5 minutes to complete and some 5 hours. Perhaps it would help if the person doing assignments had some idea of the workload assigned instead of just the number of cases. Looking at the other data we are collecting we see that cases are assigned to different “Categories”. We will add a field to the Category table that tells the average number of minutes needed to deal with a case of that category.

To do this we have to add a table to our application. The original category drop down just used a list of category names. Now, because “category” needs to support its own data, it needs to become an object represented by a table.

category_table

Now we can add the number of estimated minutes to our workloads.

average_number_of_minutes

We can see that employee 1 has 6 times the workload of employee 2, even though they have the same number of cases.

There is more information we can add to our form, so that the user, while doing their data entry can see the general context within which they are working.

form_with_extra

We have provided a view of the average time to complete a case of the assigned category, and also a view of the average response time for this customer. Hopefully, after entering multiple cases, this view will lead to questions like “why are we so often overdue in completing this category of case?” or “why is our average for closing cases on time so much better for some customer than it is for others?”

What we have done is to take some elements that would normally be visible in reports, like “what is the average time for completing this category of case?”, and made them visible to the user while they do their daily work. This does not substitute for reports, but, hopefully, it will spark the user’s curiosity.

 

Leave a Reply

Your email address will not be published. Required fields are marked *