generate automatic score from existing fields access 2010

ceuk79 · March 16th, 2013, 09:21 AM

Hi,

I am having some problems choosing the best way forward with a database and was hoping for help- I am new to databases, so please bear this in mind.

Within a table (which has 100 fields or so) I have 5 fields that need to be used to calculate a score. The field names used to calculate the score are static and will not change/ be added to.

The first field has 6 possible answers (1,2,3,4,5a or 5b- do not ask why these are not simply 1-6, as this is how it has to be!) which can be selected from a combibox. The answer has to generate a score- 1=0, 2=0, 3=0, 4=0, 5a=1, and 5b=2.
For the other field the principle is similar in that each answer must generate a score:
Field 2- number field. <0.05 = 1, 0.05 or greater = 0
Field 3- combibox, yes, no, unknown. yes= 1, no=0 (the unknowns will later be assigned as a yes or no so for the moment don't need to worry about this)
Field 4- same principle as field 2 but <7.3 = 1, 7.3 or greater= 0
Field 5- same principle as field 3

These 5 fields are currently within the same table. I need the score to be automatically generated to I can see the breakdown as well as a summed score of all 5 field scores. Having looked into this, there seem to be a few ways of doing this, and I am not sure the best way and find the explanations hard to follow.

Any help is greatly appreciated (and again, I am new to this, so you may have to be specific!)

Cheers

malcolmdixon · March 20th, 2013, 03:39 PM

A table with 100 fields does not sound normalised to me, check out http://en.wikipedia.org/wiki/Database_normalization. If each field mentioned is a question entity then you should have a separate questions table and each field would become a record in the new table. The expected answers and their scores would be in another table but have a relationship to the question table via a unique id, e.g. question_id. The actual person's answers would be another table also. This reduces redundancy and would allow you to create a query that would do the automatic calculation.

I think a better understanding of exactly what you are trying to achieve may be useful.

HTH.

Malc.

ceuk79 · March 20th, 2013, 04:43 PM

Hi- thank you for your response.

I take on board your comment about the large table. Initially I had far more tables, but the data relationships were all 1 to 1 and, after a far bit of reading, found that there does not seem to be a major advantage in having many tables for what I was doing so amalgamated them.

The database is for a medical project which will use a host of indices to see if they can predict prognosis. Now whilst I am familiar with excel and SPSS I wanted to do this in access, so that I could learn how to use it and also because a small part of the data is not a 1-1 relationship. I have 3 tables- one is for the patients' identifiable information, which I have kept as a separate table for security purposes, but has a 1-1 relationship to the large table. There is one further table which has a 1- many relationship which is for patients' re-admissions into hospital.

The information in the large table is clinical information that relates to a single admission episode and the data is static.

What I need understand is how to get the data to generate a score based on certain criteria as listed at the start of post. I had come to the conclusion that running a query was probably the way forward, but I am pretty clueless when it comes to using access to do this for me.

Does this explain my problem more clearly? Hopefully I do not have a fundamentally flawed database, but if you think I have don't be afraid to say!

Thanks

malcolmdixon · March 20th, 2013, 09:15 PM

Hi,

I understand what you are trying to do now, but I still think that the patient's answers should be a record per answer rather than in fields in one table, i.e. a 1 to n relationship patient -> answer.

Since you only have 5 answers to score you can "hard code" the scoring in a query, not ideal though.

Here's some SQL that I hope may help you. We only need to check for scoring conditions otherwise the score is 0.

Code:

SELECT large_table.patient_id, IIf([field1]="5a",1,IIf([field1]="5b",2,0)) AS field1_score, IIf([field2]<0.05,1,0) AS field2_score, IIf([field3],1,0) AS field3_score, IIf([field4]<7.3,1,0) AS field4_score, IIf([field5],1,0) AS field5_score
FROM large_table;

The code above assumes the table is called large_table that has a patient_id column name. Just add the fieldx_score fields to get the total score for that patient.

HTH.

Malc.

ceuk79 · March 21st, 2013, 05:28 PM

Hi,

Thanks very much for the help- works perfectly.

By the way, what is the problem with "hard coding" the score as a query? I may do a similar thing for some other fields to generate different score- do you think I am going to run into trouble at a later point?

Given your comments about the structure of the database I think I need to do a bit more reading around this.

Thanks