Best way to calculate and display an expression in Access?

[VBAExcellNoob]

I have a form in Access that contains about 40 questions.

I would like to display a % score depending on the answers to the questions.

For example if you answered "No" to 4 questions it would return a result of 90% or something like that.

I was going to use a field in the Table and use the Calculated to field to type in my expression.

Is this the best way to do it or is there a better way?

 

[xenou]

Probably many ways to do this. Not sure what you are going after. Do you want to create a report?

 

[VBAExcellNoob]

I usually export the data to excel and create the report in there using a series of macros.

I was just hoping to display a quick score on the form so the users would know their result based on the questions they answered.

 

[xenou]

You can use a DCOUNT() function to display calculated value on a form (two DCOUNT() functions if you want the count of questions to be dynamic).

 

[VBAExcellNoob]

You can use a DCOUNT() function to display calculated value on a form (two DCOUNT() functions if you want the count of questions to be dynamic).

Im not too familiar with the DCOUNT function, I was going to use a solution like this but it is a bit tedius:

IIf([Case type correct]="No",-5,0)+IIf( [Case visibility correct]="No",5,0)+IIf( [Correct provider group]="No",5,0)+100

So any time a question is answered "No" it will detract from a total of 100.

The only problem with this is there is nearly 40 questions so typing it out like this is very tedious

 

[xenou]

Is each question a separate column?
Your data probably needs to be normalized, but otherwise, yes, that's going to be the way.

 

[VBAExcellNoob]

Yeah each question is a separate column unfortunately. Thanks for the help anyway, time to start typing!

 

[xenou]

probably you could also adapt a strategy used in form validation:
Validate Text Box Entries | Database Solutions for Microsoft Access | databasedev.co.uk