The first thing that catches my eye is what appear to be separate tables for each exam. This could get rather difficult to maintain in the future: if the structure of exams changes you need to update n tables. Also, any aggregate queries that cover all exams for 1 patient will need to join n tables.
I'd suggest a structure like this:
patient_exams
-------------
patient_exam_id (PK)
visit_id (FK)
patient_id (FK)
study_id (Fk)
exam_seq_num
(other fields)
Use exam_seq_num
to track which exam number the record is for a patient. You can use a composite key containing patient_id
, visit_id
, study_id
, exam_seq_num
to ensure that you don't get exams for a patient with duplicate sequence numbers. You'll still need a bit of code to create the correct sequence number, maybe an on-insert trigger.
UPDATE:
Ok, so now it's clear that the exame tables are actually for different types of exams.
You could have something like
base_exam
---------
id (PK)
patient_id (FK)
exam_date
(other stuff)
Lumbar_exam_details
-------------------
lumbar_exam_id (PK)
base_exam_id (FK to base_exam.id)
(other specific fields)
blood_exam_details
------------------
blood_exam_id (pk)
base_exam_id (FK to base_exam.id)
(other specific fields)
All of your exam detail tables reference the base_exam
table, which stores common fields for all exams (such as the date of the exam, the patient who was examined, etc...).
If you really want to have a "display name" for exam types, I would do that in a view that overlays the specific exam table. For example, the query for lumbar_exam_view
might look like:
SELECT *, "Lumbar Exam" AS DISPLAY_NAME
FROM LUMBAR_EXAM_DETAILS
Use this view in any queries/reports on lumbar_exam_details
you will have access to display_name
anywhere that you want the user-friendly string.
If you need the display name to be stored as actual data, you can add an exam_type_id
field to base_exam
and then have it point to an exam_type
table:
exam_type
---------
id
display_name
Data:
exam_types
ID | display_name
------------------
1 | Lumbar Exam
2 | Blood test
Now your base exam records have an ID that points them to the correct user-friendly string. Note that this does not ensure that the exam detail record is of the correct type (i.e. it is possible, for example, to have a base_exam
record that is referenced by lumbar_exam_details
, but the base_exame
record erroneously references the display name "Blood Test"
) - it only works on the display name.
Best Answer
You can store the data in Excel as the amount of time from the default date. If you just enter the time in the format you wish, it will automatically add the default date. Enter "4:30:00" as in [FinishTime] and it will save the data like in [Unformated Date], but will be visible format you wish and retain the proper time value.
When you create the table in Access you can just change the format to change how it displays itself. Note, in access Minutes are stored as "nn" instead of "mm", distinguishing it from Months.
It will look the way you want. Note, although the unformatted default date will appear different in Access than it will in Excel, the time will remain relative to the default start point.
From there, simply import your Excel into the Access table normally or enter the time in the same "hh:mm:ss" directly into the Access table.
When you want to see the data summed up, you can do so in a query, making sure you convert it to a date and format it to display how you wish.
In this case:
Resulting in the total time that you wish
If you anticipate the total time going over 24 hours, there are more things to consider, but it doesn't sound like this project will.