Access Calculated Field with IIf and or statement

0
votes

I am working on a grading database. My students write a paper and are given a score from 6 to 1 on 7 different fields. A score of 6 is the best with 1 being the worst. The student must get an 80% and no more than one score of a 4 in any one field to pass.

edit: I am going to try and clean this up:

Currently there is one table with the following Fields:

Student_Name

Paper_Analysis (This field can have a value of 1 to 6, 6 being the best)

Paper_Purpose (This field can have a value of 1 to 6, 6 being the best)

Paper_Voice (This field can have a value of 1 to 6, 6 being the best)

Paper_Concision (This field can have a value of 1 to 6, 6 being the best)

Paper_Accuracy (This field can have a value of 1 to 6, 6 being the best)

Paper_Content (This field can have a value of 1 to 6, 6 being the best)

Paper_Reasoning (This field can have a value of 1 to 6, 6 being the best)

Paper_Score (This is a calculated field adding the Paper_Purpose, Paper_Voice, Paper_Concision, Paper_Accuracy, Paper_Content, Paper_Reasoning)

Paper_Average (This field is a percent calculated as [Paper_Score]/42

Paper_Pass (For a student to pass they must have above 80% in the Paper Average field. They are also only allowed one 4 in any of the Paper_Purpose, Paper_Voice, Paper_Concision, Paper_Accuracy, Paper_Content, Paper_Reasoning fields.)

The Paper_Pass field is what I am having trouble with. I used IIf([Paper_Average]>0.8,"PASS","FAIL"), but this does not take in to account the only one 4 rule.

Thanks for the welcome. I am willing to use SQL or whatever works best. The only caveat is I am pretty new to SQL.

1
ms-accessms-access-2010
Welcome to SO! What do you mean by "no more than one score of a 4"? Multiple 3s and 5s would be acceptable, but only one 4 is allowed? And just to make sure, you want to solve this using SQL, right? Then we would need to see the structure of the tables that are involved. Just edit your question and add the necessary information. - Leviathan
edited original post. Hope I cleaned it up enough. Thanks - Tyson Van Patten
What happens if a person gets a 1, 2 or 3? You are looking for a 4. Thus a score of 6+6+6+6+6+3+3 = 36 which equals 86% average and has NO 4's. - Wayne G. Dunn
I should say you are only allowed one 4 or less. The math is part of the problem it is possible to get about an 80% with more than one 4 or less. - Tyson Van Patten
In your table, you already have fields for Score and Average. How are those two fields calculated originally, and is there a mechanism to update them? You could add a field to count the <5 values, or have a massive IIF to find them. - Wayne G. Dunn

1 Answers

0
votes

Assuming the rule for passing is the following:

To pass, the average score has to be 80% or above and only one score is allowed to be 4 or below.

Then you could use this formula:

IIf(Paper_Average >= 0.8 AND -((Paper_Analysis <= 4) + (Paper_Purpose <= 4) + (Paper_Voice <= 4) + (Paper_Concision <= 4) + (Paper_Accuracy <= 4) + (Paper_Content <= 4) + (Paper_Reasoning <= 4)) <= 1,'PASS','FAIL') AS Paper_Pass

Why does this work?

A boolean value (that is something that can be True or False) has a numeric representation with True being -1 and False being 0. At least in Microsoft Office (which Access is a part of). I use this by summing up the result of each of the seven Paper fields being 4 or less. This is an example for the first two Paper fields:

(Paper_Analysis <= 4) + (Paper_Purpose <= 4)

If Paper_Analysis is 4 or less, then the first summand is -1. If Paper_Purpose is 4 or less as well, then the second summand is also -1, so the result is -2. If Paper_Purpose was, say, a 5, then the second summand would be 0 and the result -1.

If you do this for all 7 Paper fields and negate the result, then the result could be anything between 0 and 7, measuring the number of scores of 4 or below. To pass, this value has to be <= 1.