Patient Record/Lab Results
I have been working on a medical record for the past 9 months or so and am at an impasse. The record works pretty well in most areas, but I want to find a better way to do data input, such as lab results. My first attempt was to make a table for lab tests that linked to the patient's index, which allowed input of the date, location, signature status, and value of the test results. The problem is that the list of tests is very long, and has to be updated within the table to add any new tests that have not been previously done (and updated in the layout as well). This also didn't allow the system to flag abnormal results, nor did it account for the fact that lab normals vary by sex and age.
So I set about to try a different approach, setting up the following tables:
Table 1 (Table_Patient): the patient's main record.
Table 2 (Table_Terms): a table where each lab test's specific information is kept:
- Test Name
- Type (Chemistry, Hematology, Microbiology, etc)
- High and low Normals (as well as thresholds for "Panic" values)
Table 3 (Patient_Data): Where input of labs on a given date for a given patient are put in.
Table 4 (Results_Input): Combining the data from 1-3 for a single lab result input:
- Patient Info (from Table 1)
- Test Name/Type/etc. From Table 2
- Date of test results from Table 3
- Results of the test
- Flagging of result based on info from Table 2
This is what the tables look like in my database.
This works quite well for data input, and it does the flagging of tests that are abnormal, adds the correct units, and allows addition of tests without the addition of table rows. Nice. BUT, I am having lots of trouble figuring out how to get the information back out in a format I can use. First, I would like to see a tabular list of test results like this (OK to switch X and Y axes):
I need to be able to view lab results over time, and would like to see similar results over time as well (chemistry labs batched together, as they are often run together). I also would like to be able to answer the important questions: "when was this person's last cholesterol test?", "what was the last thyroid results?", and "when are they next due for a liver test?"
I am stuck. I can't figure out how to push this data back in this kind of format. Please help!!