Calculation Or Table Setup

[dubiousp]

Good evening I have attached in total the DB Im playing/developing had some super guides on here previously, as a newbie struggling with a simple calculation which may be because Im approaching in the wrong way.

Part of the patient assesment are GCS (Glasscow Comma) and News 2 are based on simple calculations additions for GCS the a a few >= or <= for news..

Should I be assiging values in the table some how so the calculation is easy or through code eg

GCS
first scale for eyes is 1 to 4

second for speech is 1 to 5

these get added depending on the patient response

 

[MajP]

I am guessing your GCS table should be like this

GCS_ID
GCS_Type ' Eyes, Voice,Motor
GCS_Description' To speech, spontainty...
GCS_Score ' 1,2,3,4,5

You then normalize you score table and save values
IncidentID_FK and GCSID_FK 'This requires you to add three records instead of 3 fields.

Now you can sum up total value in a simple query.

You have to normalize the first table. You can choose to leave the score table non-normal but still save the GCS_ID instead of the string you are currently saving.

 

[plog]

I second the normalization recommendation:

Database normalization - Wikipedia

en.wikipedia.org

.

That's the process of properly structuring your tables. Read up on it, google a few tutorials and apply what you learn to your tables.

Specifically, you're storing values in field names. For example, in Secondary Survey you have a bunch of Yes/No fields named after conditions. Instead those conditions should be values in a field in a new table, not field names. I would have a SecondSurveyConditions table like so:

SecondSurveyConditions
ssc_ID, autonumber, primary key
ID_SecondSurvey, number, foreign key back to existing Second Survey table
ssc_Condition, text, this will hold all the values now in names (COPD, Diabetes, etc)

That's it, that table with 3 fields now accomodates what is now 6 fields in Second Survey. Only the conditions that exist go into the table. If a patient has Diabetes a record goes into SecondSurveyConditions, if they don't a record doesn't go in. So if they have all 6 conditions, they get 6 records in SecondSurveyConditions.

That's just an example, looking at the other tables and field names, my guess is you've done this a lot. NEWS2 most likely can be done very similar into a table called Measurements.