How to calculate in a query for a report with NA values

[jenkag875]

Hello.



I have various tables where entry is completed with one of 4 values that are allowed: Correct, Partial, Wrong, NA. I have them currently set to the following in my query so it's counting NA the same was as correct which from a calculation standpoint, won't be completely accurate so I'd like to fix it. Right now I have it set to 1 for correct, .5 for partial, and 0 for wrong. NA is currently set to 1 also. In most cases, it's not affecting the overall percentages, but in some cases it could. How do I do this as accurately as possible? Here is an example of one of my formulas, just a simple If statement. I've tried having it set to -1 and also blank, but the results seem even more skewed.

CertificateInstitution Value: IIf([tblDQPersonGrowthAttainCertificate]![CertificateInstitution]="Correct",1,IIf([tblDQPersonGrowthAttainCertificate]![CertificateInstitution]="Partial",0.5,IIf([tblDQPersonGrowthAttainCertificate]![CertificateInstitution]="Wrong",0,1)))

Thanks

[ssanfu]

What should the value be for "NA"? 0?

[jenkag875]

I was able to get a way figured out for this. I had to create formulas for points possible as well as points received in order for my percentages to calculate correctly when an NA field was involved.

Thank you.