Accounting for missing values in a calculated field.

[nmartineza]

Hi,

I'm pretty new to Access, please forgive me if I misuse some of the terminology, or if I start using a lot of the analytics jargon. I have a table that stores information for multiple behavioral surveys (numerical values). My goal is to add the proper fields that compose total scores value for each respective survey (do a summation of scores).

Now, under design view for my table, I see that I can add a calculated field. When I create this calculated field, I can use the Expression Builder to do a sum of the proper fields (the fields that compose a total score for a survey). The only problem that I'm encountering is that if a field that is part of a survey is missing information, the summation disregards the rest of the values for that survey.

How can I account for these missing values so that, if 1 out of my 9 fields have information, I will still get a summation score for the 9 fields? I want to be able to do this without having to change the value of the missing field to 0.

In SPSS I can easily do this by computing a variable and using a code like this:
SUM.2(field1, field2, field3, etc.)

 

[pbaldy]

Don't like that the scores are fields, but try the Nz() function:

Nz(Field1, 0)

 

[nmartineza]

Hi Paul,

Thanks for the quick reply.
What are the limitations of using fields to store values from questions in a survey?

I'm not too familiar with the Nz function. I did a bit of research on it and it seems to help me count the number of items with an value vs those with a null value.

I found a code that the previous analyst was using to compute the total score in hopes that it helps clarify what I am trying to accomplish, thank you!

=IIf(IIf([Had poor appetite]=9,99,0)+IIf([Was unhappy and sad]=9,99,0)+IIf([Had little or no energy]=9,99,0)+IIf([Worrying prevented me from doing things]=9,99,0)+IIf([Expressed thoughts about hurting self]=9,99,0)+IIf([Felt worthless or inferior]=9,99,0)+IIf([Had difficulty making decisions]=9,99,0)+IIf([Had trouble sleeping]=9,99,0)+IIf([Felt hopeless]=9,99,0)+IIf([Changed moods quickly]=9,99,0)>0,"Missing Items",[Had poor appetite]+[Was unhappy and sad]+[Had little or no energy]+[Worrying prevented me from doing things]+[Expressed thoughts about hurting self]+[Felt worthless or inferior]+[Had difficulty making decisions]+[Had trouble sleeping]+[Felt hopeless]+[Changed moods quickly])

 

[pbaldy]

I've never done a survey app myself, but always leary of data in fields like that. What happens when they add a new question? You'll need to redesign forms, reports, etc. Duane has an example here that might be of interest:

http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html

As to your formula, the IIf() functions just return 99 or 0 depending on the answer. I suspect you'd have the same problem if any of the later answers was Null. The Nz() function replaces Null with an appropriate value, in your case 0 so the calculation would still work.

 

[nmartineza]

Paul,

This helps a lot, and thanks for providing the link to the example.
If there is one challenge that I have encountered, is the difficulty in compromising the rules of relational databases with the dynamic needs in mental health. Thanks for all the help!

 

[pbaldy]

No problem. I should warn you that trying to understand the rules can be hazardous to your mental health.

 

[nmartineza]

! It definitely is!