Microsoft Access for EMTs: Databases, Tables, Queries, Forms and Reports

Microsoft Access is a relational database management system (DBMS) for storing organised information, connecting related tables, retrieving selected records and producing forms or reports. In emergency-care education and administration, Access may support equipment registers, training records, incident logs, referral tracking, stock catalogues or audit datasets. It is not automatically an approved electronic medical record; any use of identifiable health information must follow institutional authority, privacy law, security controls and clinical governance.

Learning outcomes

• Define a database, table, record, field, primary key and foreign key.
• Explain relational design and one-to-one, one-to-many and many-to-many relationships.
• Plan tables and data types for an EMT administrative database.
• Create safe select queries and understand action-query risks.
• Design forms for accurate data entry and reports for reviewed summaries.
• Apply validation, referential integrity, access control, backup and audit practices.
• Recognise when Access is inappropriate for a clinical or operational task.

1. Database terms

2. Access database objects

3. Planning before building

1. Define the purpose, owner, users, retention period and approval authority.
2. List the subjects that need separate tables: stations, equipment, staff, inspections and incidents.
3. List fields for each subject and identify required values, units and formats.
4. Choose a primary key that is unique and stable; do not use a person’s name as a key.
5. Identify relationships and whether a record may have zero, one or many related records.
6. Define validation rules, permissions, backups, audit trail and reporting needs.
7. Build and test with fictional data before importing operational information.

4. Tables, fields and data types

Do not store several values in one field such as “gloves, masks, dressings.” Create related records or a properly designed junction table. Avoid storing calculated totals as permanent facts when they can be reliably calculated from source values.

5. Primary and foreign keys

A primary key uniquely identifies one row. A foreign key stores the matching primary-key value in a related table. Keys should be stable, required where appropriate and not based on changing descriptive text.

6. Relationships

6.1 One-to-one

One record in table A corresponds to one record in table B. It is less common and should have a clear reason, such as separating highly restricted information from general information.

6.2 One-to-many

One parent record can have many child records. One station may have many equipment items, and one equipment item may have many inspections. The primary key is on the “one” side; the foreign key is on the “many” side.

6.3 Many-to-many

Many staff may attend many courses. Resolve this with a junction table such as StaffCourse containing StaffID, CourseID, Date and Result. Never store a list of staff in one text field.

7. Normalisation and avoiding duplication

Normalisation is the process of organising data so each fact is stored in the most appropriate place and dependencies are clear. In practical terms, store a station’s address once in the Station table rather than repeating it in every equipment row. This reduces inconsistent updates.

• One field should contain one value.
• Each table should describe one subject.
• Every record needs a reliable key.
• Non-key fields should describe the key’s subject.
• Use relationships rather than duplicated names or repeated lists.
• Keep a controlled list for categories, but retain the underlying code and label appropriately.

8. Creating a database and importing data

1. Choose an approved template only after confirming it fits the requirement.
2. Create the database in an approved location with controlled access.
3. Build tables and keys before importing large data.
4. Import a copy of the source spreadsheet, map fields and inspect data types.
5. Check duplicate keys, invalid dates, empty required fields, units and encoding.
6. Compare record counts and totals between source and destination.
7. Keep the original source read-only until the migration is accepted.

9. Queries

A query asks the database for information. A select query retrieves records without changing the underlying tables. Queries can filter, sort, calculate, group and join related data.

10. Criteria and calculated fields

Criteria should be precise and documented. A query for “expired” requires a defined date and time zone. A calculation such as DaysToExpiry: DateDiff("d",Date(),[ExpiryDate]) must be tested with blank dates, time components and expired items.

• Use exact codes rather than inconsistent spelling where possible.
• Define whether date ranges include the start and end date.
• Test null or blank values explicitly.
• Check units before combining or comparing measurements.
• Save query names that describe purpose, not “Query1.”

11. Forms for controlled entry

Forms present fields in a user-friendly layout and can include labels, drop-downs, check boxes, validation and navigation buttons. A form should reduce cognitive load without hiding important information.

1. Choose the correct table or query as the record source.
2. Arrange fields in the order the user performs the task.
3. Use labels that include units and clear definitions.
4. Use a list or combo box for approved categories.
5. Make required fields visible and explain error messages.
6. Prevent accidental editing of calculated or historical fields.
7. Test with realistic and invalid entries before release.

12. Reports

Reports turn selected records into a reviewed, formatted output. A report may group equipment by station, show overdue actions or summarise monthly incidents. It should state its source, period, definitions, author and review status.

13. Example EMT database design

14. Data validation and integrity

• Set required fields for essential information.
• Use input masks only when they match the true identifier format.
• Use validation rules such as quantity greater than or equal to zero.
• Use relationships and referential integrity for linked tables.
• Prevent duplicate serial numbers where uniqueness is required.
• Record who entered or changed a high-risk record and when.
• Use inactive/archive status instead of deleting historical information unless policy requires deletion.

15. Security and privacy

Access databases may contain personal, staff or patient-related information. A file password alone is not equivalent to an institutional security system. Use approved storage, named accounts, least privilege, encrypted backups and a documented retention schedule.

• Collect only the minimum necessary data.
• Separate identifiers from teaching or analysis data where possible.
• Restrict tables and sensitive fields from users who only need a form or report.
• Do not email a database file casually or place it on a personal drive.
• Remove sample patient data before distributing a template.
• Audit permissions and revoke access when roles change.
• Protect printed reports and exported spreadsheets.

16. Backup, compact and recovery

1. Back up before imports, design changes, action queries or compact/repair operations.
2. Keep more than one protected copy, with at least one separate from the working computer.
3. Test restoration using a copy; a backup that cannot be restored is not dependable.
4. Record backup date, owner, location and retention period.
5. Use compact and repair only according to ICT guidance and never as a substitute for backup.
6. Investigate corruption, missing records or unexplained record counts immediately.

17. Access in relation to an electronic medical record

An approved EMR normally includes authentication, audit logs, role-based access, clinical workflows, backups and governance designed for patient care. A locally built Access file may be useful for a limited administrative task, but it should not silently become the official patient record or medication system. Obtain approval before integrating or importing clinical data.

18. Practical workflows

18.1 Equipment inspection

1. Open the inspection form and identify the equipment by approved ID or barcode.
2. Confirm current location and assigned vehicle.
3. Record each check, result, defect and action due date.
4. Submit the record; do not overwrite the previous inspection.
5. Run a query for overdue actions and issue a reviewed report.

18.2 Training register

1. Use Staff and Course tables, not a single repeated spreadsheet.
2. Record attendance and assessment in StaffCourse.
3. Calculate renewal due dates from approved validity rules.
4. Query staff whose certification is due to expire.
5. Restrict the report to authorised supervisors.

19. Scenarios

20. Common errors and corrections

21. Revision questions

1. Differentiate a field, record, table, primary key and foreign key.
2. Explain a one-to-many relationship using stations and equipment.
3. Why is a junction table needed for staff and courses?
4. What is the difference between a table, query, form and report?
5. List five checks before importing a spreadsheet into Access.
6. Why are action queries dangerous?
7. How can referential integrity protect an inspection database?
8. Design four tables for an EMT equipment-monitoring system.
9. Why should an Access file not automatically replace an approved EMR?
10. Describe a safe backup and recovery process.

Key takeaways

• Relational databases store each fact once and connect related records with keys.
• Tables store data, queries retrieve or calculate it, forms control entry and reports present reviewed results.
• Relationships, validation and referential integrity prevent many avoidable inconsistencies.
• Action queries require backups, testing and authorisation.
• Access is not automatically a clinical record; privacy, security and governance come first.
• Use fictional or coded data for teaching unless identifiable use is explicitly approved.

Further reading: Microsoft Access Help and Learning, official guidance on database objects, table relationships, queries, forms and reports, plus local data-governance, privacy, backup and EMR policies.