This team had 75% fewer manual tasks after Microsoft Access workflow redesign

See how we reduced a team’s manual tasks by 75% and automated their monthly reporting process to be a single button click.

Challenge

We were asked by a team to take a look at a very slow and sometimes never-loading Microsoft Access database used to track incoming requests. Once we saw the setup they had to track requests, the solution was a bit more involved to solve the team’s true problem - repetitive manual data entry.

Initial request tracking setup

When it chose to load, the Microsoft Access form was completed and a report could be generated at the end of the month.

1. When Access didn’t work, a new entry was made in an Excel workbook. This had a few more fields that were added after the database was built.
2. A Word document was then filled out manually for every request after it was completed by the team.
3. Each Word document was saved as a PDF to be referenced for any request inquiries and by management.
4. End of the month reporting (on all requests received and completed by category) was a manual task that involved a combination of counting entries in the different tools and/or counting the PDF reports.

Process

First, we investigated the Access database to see if it would load up. After a few attempts it did and we were able to see the form and fields. This prompted questions about the team’s ideal workflow of tracking and reporting requests. This lead us to discover the 4 Microsoft tools they were using to track requests in different ways (outlined above).

Next, we gathered all the existing data as the team needed to maintain their current historical records into a system that actually worked. We downloaded a copy of the data in the Access database, all the Excel workbooks, the Word document, and a PDF report.

As we went through the data to clean it, we had follow up questions for the team. Since they would be entering all this data for each request, they were on board with only keeping what was important.

Back to cleaning data. Cleaning data is the process of making sure it’s as uniform as possible, resolving formatting issues (a common problem with Excel trackers) and updating categories for the types of requests to be consistent.

At the end of all this we had a single Excel file with ALL the data ever generated by the team (well over 10 years worth) and it was ready to go.

Cleaning the data took the longest time, and while we was working on this, we were brainstorming and testing solutions using Microsoft 365 software out-of-the-box (no add-ons, third-party apps, or outside integrations).

Here’s the final workflow we came up with.

Final workflow

1. The team enters a request into a SharePoint list inside Teams. Once the request is completed, they add a completed date and save. This triggers the automatic creation of a PDF report, which is then saved (automatically) in their Teams channel.
2. At the end of every month, the team refreshes all requests and their current statuses at the click of a button in a custom dashboard. A screenshot is sufficient for monthly reporting.

Result

The new setup cuts down on entering the same data in 3 different places. There are few, if any, data quality issues as the form includes fields that are validated.

Individual request reports no longer need to be manually generated. Given the team received approximately 100 requests a month, that’s a great deal of time savings.

Finally, monthly reporting now only requires clicking the “Refresh” button once and taking a screenshot of the dashboard.

Here’s a breakdown of all the tools we used to implement this solution.

Final request tracking setup

Microsoft 365 apps used in this solution

Word, Excel, Teams, OneDrive, Power Automate, Power BI

Microsoft 365 apps breakdown

• SharePoint list in Teams to log requests using a custom formatted form
• SharePoint document library in Teams to support the PDF creation process and to store the template
• Word document template of the final PDF report
• Power Automate to automate the triggers and workflow steps. Also used to upload all historical request records to the SharePoint list
• OneDrive folder to handle converting the Word template into the PDF report (a workaround from using the paid Power Automate file converter upgrade)
• Private Teams channel created and setup to allow access to the list, document library, and store all generated PDF reports
• Step 2: Power BI dashboard for the team to update the stats and take a screenshot for end of the month reporting. (This dashboard was created on a team member’s account to get around the Premium upgrade for publishing Power BI dashboards)