Survey Calculations

[SMay]

I am trying to calculate answers in a Risk assessment survey. If the person is Male and answers YES to a particular question then the question is given a specific pre-assigned numeric value for that question (0-5). If the person is Female and answers YES then they are given a pre-assigned numeric value which may or may not be the same as the male value. Each question has different numeric values attached to the M/F fields. In all cases if the answer is NO then it is given a numeric value of 0.
At the end of the 5 questions, I am trying to total the numeric values based upon the answer to the questions. If the final numeric value totals are 0-3 then the person is given a Risk score of Low. If the results are 4-9 then they are Medium Risk. 10+ gives the respondent a value of High Risk.
Any ideas on how I would set up the table(s).
Example:
Male Q1 Yes 4
Female Q1 Yes 3
Male Q2 Yes 3
Female Q2 Yes 3
And so on. . .
Thanks in advance for your help.

 

[SMay]

Do i need to restate this or put this question in a different discussion board? I can also clarify if necessary.

Thanks in advance.

 

[Mike375]

I had a quick look at this yesterday but was not sure as to whether it was a "stand alone" deal or part of a data base.

On the surface it looks to be simple. A field for each question (plus one for male or female) and in a query a field for [Field1]+[Field2] etc and then anonther field with an IFF or a function etc.

I guess a drop down list with the questions and an AfterUpdate to put the score in and ajusted for male or female.

That is obviously a very un normalised way of doing it but I think it might be the easiest.

As I said above it would depend if this is some "stand alone" situation" or part of data base and that would determine the best way to do it.

 

[SMay]

I had a quick look at this yesterday but was not sure as to whether it was a "stand alone" deal or part of a data base.

On the surface it looks to be simple. A field for each question (plus one for male or female) and in a query a field for [Field1]+[Field2] etc and then anonther field with an IFF or a function etc.

I guess a drop down list with the questions and an AfterUpdate to put the score in and ajusted for male or female.

That is obviously a very un normalised way of doing it but I think it might be the easiest.

As I said above it would depend if this is some "stand alone" situation" or part of data base and that would determine the best way to do it.

Thanks for your feedback. Yes this is a part of a database. We have patients who will answer this survey. Based upon the answers they will be categorized as LOW-MED-HIGH risk and then we will have a treatment plan based upon the risk level.

I do have the "RiskAssessment" table with the 5 questions in their own fields as Yes/No and the Male/Female as Male(CheckYes) Female (NoCheck). I just couln't figure out how to rationalize the IFF function.


Thanks again in advance for your continued help!

 

[Mike375]

Since it is only 5 fields and given the use I would have it on the patients record. Lots here will disagree However, I see this sort of data as being an extension of LastName, DateofBirth etc and might be used for record selection.

I do have the "RiskAssessment" table with the 5 questions in their own fields as Yes/No and the Male/Female as Male(CheckYes) Female (NoCheck). I just couln't figure out how to rationalize the IFF function

I have would make/femal as the first selection then the score from the different questions would allow for that.

I assume you are only going to apply IFF to each final score, that is, IFF is applied to the result of [Field1]+[Field2] etc.

With IFF you can have seven (I think it is 7) that are nested. This one has 4 and I just pulled from one my queries. It is converting a regular premium to a yearly amount and of course is using the enrty in mode of payment

Premiums: IIf([PaymentMode] Like "H*",[RegPremium]*2,IIf([PaymentMode] Like "Q*",[RegPremium]*4,IIf([PaymentMode] Like "Y*",[RegPremium]*1,IIf([PaymentMode] Like "M*",[RegPremium]*12))))

You can also extend IFF right out by using more than one field for it and the subsequent field uses the result of the previous field.

You can also do an ulimited number with IF Then Do This End IF and also Select Case. Select Case is good because it is fucntion you ake then it is used like IFF, in other words the record set fills with results.

 

[dkinley]

Just to throw my lot in here to see if this would simplify the query action ...

Suppose on the form your had an option group for the answers. You can then use the select case on the option group prior to the data being stored in the table. You can even distinguish based on the gender control on the form.

For instance ...

Dim intTotalScore As Integer
intTotalScore = 0
 
Select Case txtGender
Case "Male"
     If optButton1 = True Then 
          intTotalScore = intTotalScore + 3 'weight of that question
     Else intTotalScore = intTotalScore + 0
Case "Female"
     ...
End Select
 
Me.txtTotalScore = intTotalScore

Where txtTotalScore could be a hidden textbox on the form unless you wanted the filler-outer to see the score.

In this sense, the query is limited to an IIF to evaluate the low/med/high based on the total score field. This would require some more massaging, just a concept. It would also mean that maintenance (answer weight change) if any would only be required at the form level.

-dK

 

[SMay]

I sincerely thank each of you for your imput. I am working on this and will let you know how it turns out!

 

[SMay]

Since it is only 5 fields and given the use I would have it on the patients record. Lots here will disagree However, I see this sort of data as being an extension of LastName, DateofBirth etc and might be used for record selection.



I have would make/femal as the first selection then the score from the different questions would allow for that.

I assume you are only going to apply IFF to each final score, that is, IFF is applied to the result of [Field1]+[Field2] etc.

With IFF you can have seven (I think it is 7) that are nested. This one has 4 and I just pulled from one my queries. It is converting a regular premium to a yearly amount and of course is using the enrty in mode of payment

Premiums: IIf([PaymentMode] Like "H*",[RegPremium]*2,IIf([PaymentMode] Like "Q*",[RegPremium]*4,IIf([PaymentMode] Like "Y*",[RegPremium]*1,IIf([PaymentMode] Like "M*",[RegPremium]*12))))

You can also extend IFF right out by using more than one field for it and the subsequent field uses the result of the previous field.

You can also do an ulimited number with IF Then Do This End IF and also Select Case. Select Case is good because it is fucntion you ake then it is used like IFF, in other words the record set fills with results.

Once again, thanks for your imput.
I created a query based upon the Survey Table. For Each Question I did used Expression builder to make Query Fields similar the following:
Q1AMale: IIf([Sex]="Male" And [Q1-A]=Yes,3,0)
Q1AFEMALE: IIf([Sex]="Female" And [Q1-A]=Yes,1,0)

At the end I created a Total Field.
After that I Created the following field:

Recommendation: IIf([Total]<=3,"Low Risk",IIf([Total]<=7,"Moderate Risk",IIf([Total]>=8,"High Risk"," ")))

It may be the long way but it worked and since it worked. It will have to stay.

Thanks for the feedback. I can't get over how nice everyone is on this Forum!