Design an Access Database for Therapy Attendance Tracking
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.
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.
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.
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.
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.
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.
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.
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.
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.

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.

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.




