Building an Exercise Catalogue for a Speech Therapy Clinic

Context

I was hired as a technical consultant by a speech therapy clinic to improve their existing patient-profile workflow.

The clinic had accumulated thousands of therapy exercises over time. The material had been created by therapists using tools they already knew, primarily Microsoft Office applications such as PowerPoint and Word. As a result, the exercises existed in a large collection of files with different formats, naming conventions, and levels of organisation.

The clinic was already using Microsoft Access to manage patient profiles. My initial assignment was to extend that system with information about the exercises assigned to each patient.

The clinic did not have a technical team, and the therapists needed to continue creating exercises using their existing tools. Any solution therefore needed to work with the material they already had rather than requiring them to adopt a new authoring system or substantially change their daily workflow.

Problem

When I accessed the computer used to manage the patient profiles, I discovered that the exercise collection was effectively an unstructured filesystem containing thousands of files.

File names followed different conventions and provided inconsistent information. Exercises were stored in multiple formats, and there was no reliable catalogue describing their contents or therapeutic purpose.

The existing process for finding an exercise was largely manual. Therapists had to search through the files based on what they remembered about file names and supplement this with handwritten notes describing what they believed individual files contained. Once an exercise had been selected, its filename was manually recorded and associated with the patient’s profile.

This made assigning exercises particularly time-consuming, especially when working with patients whose therapy required a larger or more specialised collection of exercises.

I discovered this workflow while working on the original Access database task. Rather than simply adding another field to the existing process, I asked the owner for permission to investigate the exercise collection and determine whether the catalogue could be generated programmatically.

Intervention

I approached the problem in three stages: creating a catalogue from the existing files, integrating that catalogue with the existing Access workflow, and restructuring the underlying files.

1. Building an Exercise Catalogue

The first step was to determine whether useful information could be extracted from the existing files without requiring the therapists to manually catalogue thousands of exercises.

I developed a series of Java programs using Apache POI to inspect and extract information from the Office documents. The programs collected metadata and content that could be used to identify and classify the exercises.

Working with one of the therapists, I developed a set of classification rules based on the exercise names and the information contained within the documents. The first classification pass successfully categorised approximately 80% of the exercises into therapy-related categories.

I then reviewed the results with the therapist, identified the types of files that had failed classification, and refined the filtering rules. The second pass classified almost the entire collection, leaving a smaller set of files that could be reviewed manually.

This allowed the majority of the catalogue to be generated from the existing material rather than manually processing every file.

Because the clinic owner and therapists were not technical users, I also built a simple Swing-based Java interface for handling future additions. The interface allowed them to select a file and inspect its extracted metadata, providing a straightforward way to identify and classify new material without interacting directly with the underlying tooling.

I also prepared the software to integrate the catalogue with the existing patient-management system.

2. Integrating the Catalogue with Microsoft Access

Rather than replacing the clinic’s existing Access system, I extended it.

I redesigned the database structure by adding tables for exercises and their relationships with patient profiles. I then added the corresponding fields and interface elements to the existing Access workflow using Access/Visual Basic.

This meant therapists could continue working with patient profiles in the same environment they were already familiar with, while gaining access to the new exercise catalogue and assignment information.

The goal was to introduce the new capability without disrupting the existing patient-management workflow.

3. Restructuring the Exercise Files

Once the catalogue and database structure were ready, I created another Java program to migrate the existing exercise collection.

The program used the catalogue generated during the previous stage as its source of metadata. For each exercise, it:

1. Read the extracted metadata.
2. Determined the appropriate exercise category.
3. Created a corresponding directory structure.
4. Grouped files by format within the relevant category.
5. Generated a consistent filename containing useful identification information.
6. Preserved the original file extension.
7. Copied the file to its new location.
8. Created the corresponding database record.
9. Added a file:// reference so the exercise could be opened directly from the patient workflow.

Because the collection contained thousands of files, I did not want the migration to depend on being able to manually verify every rename and move.

I therefore also generated a mapping between the original filename and its new catalogue identifier/name. This provided a record of the migration and made it possible to investigate individual edge cases by tracing a new file back to its original location and name.

Outcome

The clinic moved from manually searching through an unstructured collection of files and recording exercise assignments to navigating a structured exercise catalogue directly from the patient-management workflow.

Exercise assignment that had previously taken hours could be completed in a few minutes for straightforward cases. More complex cases that required a larger selection of exercises could still take longer, but the process was reduced to roughly one or two hours rather than requiring the previous manual search process.

The existing Access-based patient workflow remained in place, while the exercise collection gained:

• a searchable and structured catalogue
• consistent categorisation
• organised filesystem storage
• consistent file naming
• direct links from patient records to exercises
• a repeatable process for handling newly added files
• a migration map preserving the relationship between the original and new file structures

Most importantly, the solution allowed the therapists to continue creating exercises using the tools they already knew, while the underlying collection gained the structure that had previously been missing.