Please Help! Designing a medical database

[DrJ]

I am designing a database for patient vital signs (height, weight, BP, etc). The closer I come to finishing, the more complicated the project gets. Makes me think I'm doing something wrong design-wise.

DATABASE GOALS
1. Store patient identifying data (name, med record, date of birth, etc.)
2. Store multiple sets of vital signs for each patient
3. Show a patient record with:
- patient ID data
- All his/her vital signs data

DESIGN
1. Table: "tbl_patientData"
2. Table: "tbl_vitalsData" (linked to "tbl_patientData" by [medRec] field)
3. Form: frm_patientRecord with
a. Fields from "tbl_patientData"
b. Subform "frm_vitalsData subform" (linked to main form by [medRec])

The above works fine, my question is what is the best way to do the analysis/calculations based on the above data?

ANALYSIS GOALS
1. Show patient's most recent (i.e. newest) height, weight, BP, etc
2. Calculate Body-Mass-Index (BMI) based on Height and Weight


2. Calculate BMI percentile based on BMI, age, gender, and norms table
3. Calculate BP percentile based on age, gender, and norms table

Problem #1: "tbl_vitalsData" has many empty fields because, for a vitals-entry-date, the record may only include certain values (e.g. BP but not Ht, Wt or Wt but no Ht). Having this table full of holes makes it hard to calculate the above values because I have to decide
a) which value(s) to use
b) which date to base calculation of age on.

Problem #2: Because there are so many calculations, the database becomes slowed down.

QUESTIONS
1. What is the best way to get the most recent value from each column of a table?
2. Where is the best place to do calculations on field data? In a query, on a form? Some other place?
3. How can I save these calculations so that they only get re-calculated if the field data changes?

[NTC]

QUESTIONS
1. What is the best way to get the most recent value from each column of a table?
** have a timestamp field in the table that gets auto entered with Now() when the record is created - and then one can find it using MAX in a query

2. Where is the best place to do calculations on field data? In a query, on a form? Some other place?
** depends. If it needs to be a recorded value then in the form during data entry (if possible). If it doesn't need to be recorded then it should occur either in query or form upon display. There is no single correct answer to this. It is situational.

3. How can I save these calculations so that they only get re-calculated if the field data changes?
** whether in form, report or query - they should calc when triggered. If the input data changes then the resulting value will always update when displayed. But if you record the result in a table then you must force a recalculation after new data in entered.

[RayMilhon]

You don't include the structure of your tblVitals but this is how I would do it
PatientID
TestID
Value
TestDate

Patient ID is a foreign key to the patientdata
TestID is a foreign key to a Tests table which would contain the tests you want to track. BP, Weight, Height
Value would be the specific value of the test
TestDate would be the date of test.

Primary Key would include PatientID, TestID and TestDate.

I would never put anything that can be calculated from 2 other fields into a table. It is redundant and unnecessary.

Now as to querying the data. The best way would be to create a query that gets the last test result for a specific Test.

Select PatientID, Value, Max(Testdate) as Tdate from tblVitals Where ValueID = 1
Group By PatientID, Value

Finally Create a query that links all of those Test Queries by patient ID and that gives you the last result from each test. I'm sure somebody with more experience with Access than I have can come up with something more efficient but that'll work.

[RayMilhon]

Oops ValueID = 1 should have been testid = 1. That's what I get for not re-reading my post.