How to Design an Access Patient Database for Multiple Treatment Dates
Question details
The user needs to build a patient-tracking database in Access that handles repeated visits, tests, and follow-up measurements without creating separate tables or forms for each treatment stage.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating a scalable clinical data collection and patient tracking system for multiple, recurring treatments.
- Observed behavior
- The goal is to implement a normalized relational structure using linked tables and subforms, avoiding flat tables that mimic poorly designed paper forms.
Before building your database, outline all the unique data entities you need to collect (patients, visits, tests, results) and sketch how they relate to one another to ensure proper normalization.
Implement a Normalized Relational Database Structure
Create a scalable relational design using main tables and lookup tables to track unlimited treatment dates instead of using flat, paper-like forms.
Reproducing paper forms directly into a single database table often leads to a poorly designed, non-relational database. Instead, break your data into logical entities so you can record unlimited treatment dates without constantly modifying the table structure.
Set up a 'tblPatients' table in Access with 'PatientID' as the Primary Key, along with demographic fields like Patient Name, Date of Birth, and Contact Info.
Build a 'tblTests' table containing 'TestID' (Primary Key) and 'TestName' to standardize the types of measurements and test options available.
Set up a 'tblTestResults' table with 'TestResultID' (Primary Key), 'PatientID' (Foreign Key), 'TestID' (Foreign Key), 'TestResult', and 'TestDate'. This allows you to record any test at any time.
Create a main Patient form based on 'tblPatients'. Then, embed a subform based on 'tblTestResults' to easily input and view multiple treatment dates and test results for that specific patient.

Need a Free Office Suite for Your Clinical Reports and Data?
While Microsoft Access is a specialized tool for complex relational databases, WPS Office is an excellent, free, and lightweight alternative for handling everyday clinical reports, simple patient logs, and data analysis through familiar Word, Excel, and PDF equivalents.
- 1. Download and Install: Download WPS Office for free from the official website and follow the lightweight installation prompts.
- 2. Open or Create Patient Logs: Launch WPS Spreadsheet to seamlessly import existing Excel patient logs or build new structured tracking sheets.
- 3. Export to PDF: Save your completed clinical reports and medical data charts as secure PDFs directly within the application.

Frequently Asked Questions
Why shouldn't I just create an Access table that mimics my paper form?
A database table that exactly mirrors a paper form often results in "flat" data with dozens or hundreds of redundant columns. This violates relational database normalization principles, making data difficult to query, report on, and expand over time.
How do I link patient records to their test results in Access?
You link them using a Foreign Key. Add the 'PatientID' from your Patients table as a field in your TestResults table. Then, establish a one-to-many relationship in the Database Tools tab under 'Relationships'.
What is the advantage of using a subform for treatment dates?
A subform allows you to view the primary patient details on the main form while seamlessly adding, viewing, or editing multiple rows of test results and treatment dates directly beneath it, without needing to switch between different forms or windows.
Can I track different types of tests using this database structure?
Yes. By utilizing a separate 'tblTests' lookup table, you can add new test types at any time without changing your database schema. When entering results, you simply select the new test from a dropdown menu, ensuring consistent data entry.




