Score rating system 1

[Triacona]

Dear All,

Please could you help, any help would be appreciated

I have a query, SotCpRating.

I want to create a rating field, based on an IIF statement.

There are 8 questions, named sequentially Q1,Q2,Q3 etc...
There is an overall score SCORE

The rating is calculated as follows...

If the SCORE<=15 and any of the questions Q1 to 8 <=5 then it get a 5 Rating.

If the SCORE<=20 and any of the questions Q1 to 8 <=10 then it get a 4 Rating.

If the SCORE<=30 and any of the questions Q1 to 8 <=10 then it get a 3 Rating.

If the SCORE<=40 and any of the questions Q1 to 8 <=15 then it get a 2 Rating.

If the SCORE<=50 and any of the questions Q1 to 8 <=20 then it get a 1 Rating.

If the SCORE>50 and any of the questions Q1 to 8 >20 then it get a 0 Rating.

This is where my problem shows its head
I have the current code:

Rating:iif([Q1],[Q2],[Q3],[Q4]<=5,iif([SCORE]<=15,5))

As you can see I have only began the first rating (5)
It states

I have the wrong number of arguments.

Am I doing this right?
How do I check multiple fields within an IIF statement?

Any help would really be appreciated.

Thank you in advance!

Kind regards
Triacona

 

[MajP]

this should work, but you should normalize your database. Also the logic does not make any sense to me. What happens to a score of 12, but no Q below 5. It gets no score. Put in a standard module

Public Function getScore(Score As Variant, ParamArray questions() As Variant) As Variant
  Dim Q As Variant
  Dim minScore As Integer
  minScore = questions(1)
  If Not IsNull(Score) Then
    For Each Q In questions
      If minScore > Q Then minScore = Q
    Next Q
  End If
  Select Case Score
   Case 0 To 15
     If minScore <= 5 Then getScore = 5
   Case 16 To 20
     If minScore <= 10 Then getScore = 4
   Case 21 To 30
     If minScore <= 10 Then getScore = 3
   Case 31 To 40
     If minScore <= 15 Then getScore = 2
   Case 41 To 50
     If minScore <= 20 Then getScore = 1
   Case Is > 50
     If minScore > 20 Then getScore = 0
   Case Else
 End Select
End Function

Rating: getScore([Score],[Q1],[Q2],[Q3],[Q4],[Q5],[Q6],[Q7],[Q8])

 

[SkipVought]

Hi,

I might be inclined to make a table for the rating and scores.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

I have the wrong number of arguments
The IIf function takes 3 arguments: IIf(expr, truepart, falsepart)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear MajP,

Thank you for your great help.

I have tried your Function; saved it as Standard in modules(only way it seems to work).

Changed Score to SCORE, as the database I am working off uses caps for all fields.

So as below.

Public Function GetScore(SCORE As Variant, ParamArray questions() As Variant) As Variant

  Dim Q As Variant
  Dim minScore As Integer
  minScore = questions(1)
  
  If Not IsNull(SCORE) Then
  
    For Each Q In questions
    
      If minScore > Q Then minScore = Q
      
    Next Q
    
  End If
  
 Select Case SCORE
   Case 0 To 15
     If minScore <= 5 Then GetScore = 5
   Case 16 To 20
     If minScore <= 10 Then GetScore = 4
   Case 21 To 30
     If minScore <= 10 Then GetScore = 3
   Case 31 To 40
     If minScore <= 15 Then GetScore = 2
   Case 41 To 50
     If minScore <= 20 Then GetScore = 1
   Case Is > 50
     If minScore > 20 Then GetScore = 0
   Case Else
   
 End Select
 
End Function

I have put the field in my query -

Rating: GetScore([SCORE],[Q1],[Q2],[Q3],[Q4],[Q5],[Q6],[Q7],[Q8])

I run it and the query has pop up screens asking for data with Q1,Q2...etc as headings.

The query then; if I leave all the data input boxes for all questions blank spurts out a debbuging error...

Run time error 94
Invalid use of Null

I then click debug
The following is highlighted..

[b] Public Function GetScore(SCORE As Variant, ParamArray questions() As Variant) As Variant

  Dim Q As Variant
  Dim minScore As Integer
 [b][i] minScore = questions(1)[/i][/b]
  
  If Not IsNull(SCORE) Then
  
    For Each Q In questions
    
      If minScore > Q Then minScore = Q
      
    Next Q
    
  End If
  
 Select Case SCORE
   Case 0 To 15
     If minScore <= 5 Then GetScore = 5
   Case 16 To 20
     If minScore <= 10 Then GetScore = 4
   Case 21 To 30
     If minScore <= 10 Then GetScore = 3
   Case 31 To 40
     If minScore <= 15 Then GetScore = 2
   Case 41 To 50
     If minScore <= 20 Then GetScore = 1
   Case Is > 50
     If minScore > 20 Then GetScore = 0
   Case Else
   
 End Select
 
End Function

If I exit the debugger; Access goes into the query and the following error message appears.

This expression is typed incorrectly, or is to complex to be evaluated, for example, a numeric expression may contain to many compicated elements. Try simplifying the expression by assigning parts of the expression to varuiables.

The way I designated questions was.

Q1: IIf([ID]="1",[Q Score])

the same for each question i.e.

Q2: IIf([ID]="2",[Q Score])

etc.
I also have problems adding these scores together...
i.e.

BcScore: [Q5]+[Q6]+[Q7]

I get a blank result.
Does this relate in any way to the errors you have.
I also checked [Q Score] and it has a numeric field in the tables.

Thank you for all your help and continued help


Kind regards

Triacona

 

[MajP]

I am kind of confused on how your table is set up. The error is because of q1 being null. Can you explain the fields in your table? Can you explain this IIf([ID]="1",[Q Score]). What is ID and what is Q Score, and why is it a string value not numeric?

I assumed you had a table or query with fields

Score
q1
...
q8

And that none of these are null. But obviously this is incorrect.

 

[Triacona]

Dear MajP,

Thanks for the help

ID is a string, tried it without quotes "" and it errored as wrong data type. (it is the question number, but for some reason the table creator made it a text field??)

Q Score is the questions score, can be a 0 value.

Q1-8 have the below structure and can have a null value as demonstrated.
Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
10
0
5
0
0
0
5
0
30
0
5
0
5
5
5
0
30
0
5
0
5
5
5
0








18
18
18
10
20
20

5
30
0
5
22
5
0
5
0
So the code for each question will run through the table and for each instance of REFVAL (case reference) it will allocate the score according to ID.

Q1: IIf([SEQNO]=2,[Q Score])

As you can see the else is not used and therefore give a null value.
I have tried

Q1: IIf([SEQNO]=2,[Q Score],0)

but that gives each instance a 0 value, therefore give an incorrect rating system (giving multiple answers to the question.

So this bascially takes the scores breaks them down into manageable questions, not continuous data from the table, so I can add subtract and check them.

I hope this helps you.

Thanks for all the help.

Kind regards
Triacona

 

[MajP]

I am sorry, but I do not understand. I have seen some strange table designs, but I can not make heads or tails of that. Can you please show the table structure? Something like. You keep referencing fields that are not mentioned in prior posts.

tblName
FldOneName (datatype, purpose)
FldTwoName (datatype, purpose)
FldXName (dataType, Purpose)

The normal way this is done, assuming different people have different answers.

tblQuestionScores
personID_fk (long, foreign Key to the person table)
questionID_fk (long, foreingn key to the question table)
scoreID (long, the persons score for the given question)

example
1 1 20 (person 1, question 1, score of 20)
1 2 15 (person 1, question 2, score of 15)
...
20 8 5 (person 20, question 8, score of 5)

 

[MajP]

Bottom line. If you design your tables correctly, then all of these cludged functions will go away and you can likely do this with simple queries. I would look at normalizing your tables, and not waste time with these workarounds. If not it just becomes a cascading amount of workarounds to do relatively simple things. Once you make correct tables, you can use append/update queries to move your data into the new tables.

 

[Triacona]

Dear MajP,

Thanks for all your help...
Unfortunately I didn't design the tables...otherwise yes this would have been much, much simpler.

I am pulling tables from and ODBC database, it is a system created by a third party for us, yes, you would think an entire firm would get it right and give us a tested and workable product, but they didn't.

I have gotten around some of the problems.
Q5,Q6 and Q7 are the Scores that need to be looked at.
I created three queries.
SotQ5
SotQ6
SotQ7
Then I created a new Query SotCpRating
I added SotQ5,SotQ6,SotQ7 to the new quary linked SotQ5,SotQ6,SotQ7 by their CP Ref field.
So it pulls through all scores for Q5,6,7. for all cases.
Now I have tried to use your GetScore Function, within this.
The fields in SotCpRating are as follows.

CP Ref
TRADEAS
Q5
Q6
Q7
TotalBcScore

TotalBcScore = SotQ5.QScore + SotQ6.QScore + SotQ7.QScore

So the following...

If the TotalBcScore<=15 and Q5 or 6 or 7 <=5 then 5 rating

If the TotalBcScore<=20 and Q5 or 6 or 7 <=10 then 4 rating

If the TotalBcScore<=30 and Q5 or 6 or 7 <=10 then 3 rating

If the TotalBcScore<=40 and Q5 or 6 or 7 <=15 then 2 rating

If the TotalBcScore<=50 and Q5 or 6 or 7 <=20 then 1 rating

If the TotalBcScore>50 and Q5 or 6 or 7 >20 then 0 rating

I don't really now how to use the variable within the function below.

Please could you help
I use the function in the query SotCpRating as such...

Rating: GetScoreEt([SotQ5].[QScore]+[SotQ6].[QScore]+[SotQ7].[QScore])

The function is below.

Public Function GetScoreEt(QScore As Variant, ParamArray questions() As Variant) As Variant

  Dim Q As Variant
  Dim minScore As Integer
  'minScore = i
  x = Qi
  minScore = x
  For i = 5 To 7
     
    
 Select Case TotalBcScore
   Case 0 To 15
     If minScore <= 5 Then GetScoreEt = 5
   Case 16 To 20
     If minScore <= 10 Then GetScoreEt = 4
   Case 21 To 30
     If minScore <= 10 Then GetScoreEt = 3
   Case 31 To 40
     If minScore <= 15 Then GetScoreEt = 2
   Case 41 To 50
     If minScore <= 20 Then GetScoreEt = 1
   Case Is > 50
     If minScore > 20 Then GetScoreEt = 0
   Case Else
   
 End Select
 
 Next
 
End Function

The end result is that Rating is equaled to 5 for everything including some 3 rated premises.
So it is not working as desired...

Please could you aid me in this function, I am a newbie at creating functions...

Thanks for all your help and future help, it is much appreciated.

Kind regards

Triacona

 

[PHV]

What is the schema of the table having the [ID] and [Q Score] columns ?


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Yes if you clearly answer PHV's question then we can show you how to normalize the data using a Union query. Then this gets a lot easier and more flexible.

 

[MajP]

Example

http://www.fabalou.com/Access/General/Normalization_Howto.asp

 

[PHV]

My guess:
CP Ref (case reference)
ID (question number)
Q score (score)
What about this ?

SELECT [CP Ref], Sum([Q score]) AS TotalBcScore
, IIf(Sum([Q score])<=15 AND Min([Q score]<=5, 5
, IIf(Sum([Q score])<=20 AND Min([Q score]<=10, 4
, IIf(Sum([Q score])<=30 AND Min([Q score]<=10, 3
, IIf(Sum([Q score])<=40 AND Min([Q score]<=15, 2
, IIf(Sum([Q score])<=50 AND Min([Q score]<=20, 1
, IIf(Sum([Q score])>50 AND Max([Q score]>20, 0
, -1)))))) AS rating
FROM yourTable
WHERE ID IN ('5','6','7')
GROUP BY [CP Ref]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV and MajP,

Thanks for all your help

@PHV thanks for the

IFF AND

tip! that helped alot as you will see below.

I have solved it as above I created 3 queries:

SotQ5
SotQ6
SotQ7

Then I created a new Query SotCpRating
And Added the above Queries and linked them by CP Ref.

I then created a field...

TotalBcScore: SotQ5.QScore + SotQ6.QScore + SotQ7.QScore

I then created another field...

Rating: IIf([SotQ5].[QScore]<=5 And [SotQ6].[QScore]<=5 And [SotQ7].[QScore]<=5 And [TotalBcScore]<=15,5,IIf([SotQ5].[QScore]<=10 And [SotQ6].[QScore]<=10 And [SotQ7].[QScore]<=10 And [TotalBcScore]<=20,4,IIf([SotQ5].[QScore]<=10 And [SotQ6].[QScore]<=10 And [SotQ7].[QScore]<=10 And [TotalBcScore]<=30,3,IIf([SotQ5].[QScore]<=15 And [SotQ6].[QScore]<=15 And [SotQ7].[QScore]<=15 And [TotalBcScore]<=40,2,IIf([SotQ5].[QScore]<=20 And [SotQ6].[QScore]<=20 And [SotQ7].[QScore]<=20 And [TotalBcScore]<=50,1,IIf([SotQ5].[QScore]>20 And [SotQ6].[QScore]>20 And [SotQ7].[QScore]>20 And [TotalBcScore]>50,0))))))

The above rated everything correctly, it works!!

Thanks for all your help, it is much appreciated.

Kind regards

Triacona