Others

Design an Access Database for Therapy Attendance Tracking

Natalie TaylorNatalie Taylor Nov 22, 2026 868 views

Question details

Design a database to track patient admissions, therapy assignments, attendance, census, and treatment hours.

Product
Microsoft Access
Device & OS
not provided
Scenario
A hospital needs to organize therapy and admission records and produce census and treatment reports.
Observed behavior
The desired database should link patients to admissions and therapy sessions while calculating census and attendance from stored records.
Before you start

Define whether the discharge date counts as a census day and whether treatment hours are based on scheduled duration or actual attendance. Confirm which patient information and access controls are permitted under your hospital's privacy policies.

Solution 1Recommended

Create related tables and calculate reports with Access queries

Use a normalized design when patients can have multiple admissions, group assignments, and therapy sessions.

Store individual events and relationships in tables, then calculate census, attendance counts, and treatment hours in queries rather than storing duplicate totals.

Use AutoNumber primary keys and matching Number fields with the Long Integer field size for foreign keys. Use Date/Time fields for dates and times.

1
Create patient and admission tables

In Access, select Create > Table Design. Create a Patient table with PatientID as the primary key and the necessary demographic fields. Create an Admission table with AdmissionID, PatientID, admission date, discharge date, and a unit reference. Allow a blank discharge date for an ongoing admission.

2
Create therapy tables

In Create > Table Design, create a TherapyGroup table and a TherapySession table. Give each session a SessionID, GroupID, session date, start and end times, and references to its clinician and location. Create lookup tables for units, clinicians, and group types where useful.

3
Record assignments and attendance

Create an Assignment table with AssignmentID, AdmissionID, GroupID, and assignment start and end dates. Create an Attendance table with AttendanceID, AssignmentID, SessionID, attendance status, and actual attended minutes when needed for reporting.

4
Link and validate the tables

Open Database Tools > Relationships, add the tables, and connect each primary key to its corresponding foreign key. Select Enforce Referential Integrity where appropriate. Add a unique index on AssignmentID and SessionID in the Attendance table, and validate that the assignment and session belong to the same group and fall within the relevant dates.

5
Build a census query

Select Create > Query Design and add Admission. For a census date, filter for admissions on or before that date and discharges that are blank or on or after it, if your policy counts the discharge date. If fields include times, use day boundaries consistently. Count admissions or distinct patients according to the report definition.

6
Calculate patient days and treatment hours

In Create > Query Design, use a date-by-date census query or equivalent calculation to sum daily census values across the reporting period for patient days. Join Attendance to Assignment and TherapySession for attendance reports, and sum actual attended minutes divided by 60 for treatment hours when actual time is the chosen measure.

Create related tables and calculate reports with Access queries
Confirm counting rules: Census and patient-day totals depend on the hospital's admission and discharge rules. Decide how to handle overlapping admissions and missed or partial sessions before finalizing reports.
Free Microsoft Office alternative

Use WPS Office for Supporting Therapy Reports

WPS Office is a free, lightweight alternative with a familiar interface for working on supporting spreadsheets and documents. It can ease migration of compatible Office files, but it does not replace or fix a relational Microsoft Access database.

Review exported attendance and census data in WPS SpreadsheetsOpen and edit compatible Microsoft Excel XLSX and Word DOCX filesUse a familiar interface for everyday document and spreadsheet workWork with a lightweight office suite available for free
microsoft office alternative - wps office

Frequently Asked Questions

Which Access field type should I use for admission and discharge dates?

Use Date/Time fields in Table Design so you can filter dates and perform date arithmetic. Leave the discharge date blank for an admission that is still active.

How do I calculate the census for a specific day?

In Create > Query Design, filter admissions that started on or before the census date and have no discharge date or a discharge date on or after it. Adjust the discharge condition to match your hospital's counting policy.

How are patient days different from the number of admissions?

Patient days are the sum of daily census counts over a reporting period. One admission can contribute multiple patient days, depending on its dates and the hospital's counting rules.

How can I prevent duplicate attendance entries?

In the Attendance table's Design View, create a unique composite index on AssignmentID and SessionID. Also validate that the assignment and session refer to the same group.

Can WPS Office run the Access database?

WPS Office is not a replacement for an Access relational database. You can use WPS Spreadsheets to work with compatible exported files, subject to your hospital's data-handling policies.