How to code a field to make coments on scores

[grenee]

There is a Student's test score field called Scores. Next to each score there needs to be a comment based on each score. That is there is another field called Comments where the comments would vary for each score that is different.

Is this possible?

 

[AccessMSSQL]

Can you provide more information on what you are trying to do? Are you trying to add this to a form, report? It sounds like you will need to create a comment field for each test score. What does your table structure look like? Do you have a separate table for test scores?


http://www.accessmssql.com/

 

[grenee]

The Table consists of 2 fields: Name and Scores. In the report there needs to be a field with a comment for each score, so that a person who scores 90 is given the comment 'Excellent', the score of 80 is given 'Good', the score with 50 is 'Poor' and the person with 'A' is given 'Absent' etc. Essentially I want to be able to write a conditional if or case statement in VB to be able to use other criteria to generate comments.

 

[AccessMSSQL]

The best way to do this would be to create a lookup table with score and comment.
Two fields: Score, Comment
Values would be
90 Excellent
80 Good
70 Fair
60 Poor
50 etc
A Absent

If you had values inbetween you would have to add all of the scores as such:
100 Excellent
99 Excellent
98 Excellent
97 etc...
96
95
94
93
92
91
90
etc..

In the query behind your report just add the lookup table and join on score to the score field in your student scores table.

The score field would have to be a text field since it looks like you can store alphanumeric values (eg: 'A' for absent)

If you have new values to add - you would just add these to the table and they would automatically appear in your report.


http://www.accessmssql.com/

 

[AccessMSSQL]

You could also use VBA but if you are going to have additional criteria you would have to modify the code periodically:

Here's a function you could add and then call it from your query or report:

Public Function getScore(score as string) as string
Dim strComment as string
If isnumeric(score) then
   if score >= 90 then
       strComment = "Excellent"
   elseif score >=80 and <90 then
       strcomment = "Good"
   elseif....etc
   end if
else 'if score is not numeric
   if score = "A" then
      strcomment = "Absent"
elseif score = "etc....
   end if
end if
 
getScore=strComment
End Function

 

[Beetle]

There are several ways you could do this. I would suggest you create a small "lookup" table that has a field for the score value and a field for the comment, join this table to your original table in a query using the Score field, then base your report on that query. The advantage here, especially if you want to use this in more than one object, is that if you want to modify or add options in the future you simply modify or add rows in the table. You don't have to track down and rewrite code.

 

[Beetle]

Geez, that's the second time I've been beaten to the punch today. Maybe a beer would speed up my reaction time.

 

[grenee]

Thanks. I will get to work and hopefully by tomorrow I will be done.

 

[grenee]

I think my problem needs restating so here it is:

There is a table with 2 fields: Name and scores. There is a query based on this table, and it has an additional field called comments. All this comments field is required to do is to state: if the score is "" then the score is "Absent"

 

[AccessMSSQL]

Here is a sql statement with your iif statement. The iif statement will get un-weildy if you keep trying to add more criteria to it.

SELECT studentscores.Student, studentscores.Score, IIf([score]=90,"Excellent",IIf([score]=80,"Good",IIf([score]=70,"Fair",IIf([score]=60,"Fair",IIf([score]<=50,"Poor","Absent"))))) AS Expr1
FROM studentscores;

Perhaps you don't understand the lookup table concept? It would be much easier, beleive me. Just create another table as follows:

Field: Score (this field should be same datatype as score in your main table)
Field: Comment
Call it: ScoreComments

Populate this table with the Comments you want to appear for each score:
Score Comment
90 Excellent
80 Good
70 Fair
..etc.

In the above query - instead of doing the nested IIF statement as I've shown above do this instead:

SELECT studentscores.Student, studentscores.Score, IIF(studentscores.score="","Absent",scorecomments.comments) as scorecomment
FROM studentscores LEFT JOIN scorecomments ON studentscores.Score = scorecomments.score;

 

[grenee]

What i am not following yet is how do I connect the code to the field

 

[AccessMSSQL]

Are you trying to connect the code to the field in your report? You need to set the report recordsource equal to the query that you are basing your table off of. Then, in the comments field you have in your report - set the control source = to the expression field that we created in the above example. Perhaps you might post the project here and I can provide an example.