Pandemic Response Automation
COVID-19 automation compresses seven daily reports into a single readiness lens
During the COVID-19 pandemic I built a Power Query model inside Microsoft Access that reconciled seven isolation reports and the nominal roll for 550 personnel. Duty clerks moved from four-hour spreadsheets to a refreshed, audit-ready view in minutes.
Executive Summary
Power Query + Access rebuilt the pandemic battle rhythm in less than a week.
The model ingested seven isolation feeds and the unit nominal roll, validated them through shared business logic, and published a consolidated readiness score before each duty handover. Clerks stopped rebuilding spreadsheets and focused on advising leaders.
Feeds consolidated
7 isolation + nominal datasets each dayManual effort reduced
97% drop in consolidation timeHours reclaimed
1,300+ during lockdown rotationOutput latency
< 2 min from refresh to publishPandemic Metrics
Visuals quantify how the model collapsed manual work into a trusted readiness picture.
Swipe or scroll sideways to move through the evidence cards.
Scroll horizontally ⟶
Context and Challenge
When the UK Government dispersed units, duty clerks rotated one week on and nine weeks off. Each on-duty day required manually reconciling seven isolation reports and a nominal roll—roughly 550 people—inside spreadsheets that took four to five hours to complete. That workload consumed most of the shift, delayed insights, and risked inconsistent situational awareness for commanders.
Solution Development
Guided by a mentor, I architected a Power Query model hosted in Microsoft Access that ingested each report, normalised field names, and enforced shared logic. The model regenerated the readiness view in under two minutes and output the format the unit already trusted. I then captured the workflow in a Business Process Guide so any clerk could follow the exact steps.
Initial Challenges and Stakeholder Engagement
Early progress was invisible to stakeholders; by midday on Day 1 I could only describe entity relationships, not a working output. I used that brief trust gap to narrate the architecture, then demonstrated the automated refresh that afternoon. From there I paired administrators through remote walk-throughs so every shift partner understood the data lineage and governance considerations.
Efficiency Gains and Impact
The automation reduced consolidation from four hours to two minutes while the export step remained a consistent five-minute task. Over lockdown that equated to more than 1,300 hours returned to the unit and a fresh report before each command brief. The comparison resonated when we played back the numbers:
| Step | Manual | Automated |
|---|---|---|
| Export | 5 min | 5 min |
| Consolidation | 240 min | 2 min |
| Total | 245 min | 7 min |
Technical and Professional Philosophy
This project reinforced that clarity, modular logic, and expressive audit design matter most in crisis. Rather than chase new tooling, we maximised Microsoft 365, emphasised ceremony around the Business Process Guide, and ensured every transformation was explainable to leadership and auditors alike.
Narrative Framing for Stakeholders
- “From 4 hours to 2 minutes per daily readiness build.”
- “97% reduction in manual effort with zero loss of fidelity.”
- “1,300+ hours returned to the unit across lockdown rotations.”
- “Zero-touch consolidation with auditable lineage and guardrails.”