I have an audit database. Users score each question 0 (fail) to 3 (perfect) for 11 questions. Some questions may not be applicable but I don't believe I can use N/A in a number field (that I need to caluclate) so I'm scoring N/A as 4. I want to add the total scores for each question to obtain a total score for the audit; but I only want to include scores where the value is less than 4. I was thinking of using IIF (eg IIF(Q1<4....)but I am unsure how to phrase it or if that is the best way to do it. Any help or guidance would be greatly appreciated. My fields that I need to calculate are Q1_Answer, Q2_Answer, Q3_Answer etc. I have been running this year based on scoring N/A as a 3 (perfect) as a work around but I would like to use N/A or a score for N/A going forward so a total based on only actual scored values is shown. In Excel I would just run a SUMIF but I'm not sure of the equivalent in Access.

CodePudding user response：

You can use NULL in a number field, so long as the database is set up to allow nulls, or in MS Access terms, Required = No.

Then use NULL instead of 4 to represent N/A.

The SQL AVG aggregate function will ignore the nulls.

In a report or form showing individual scores, you can use the number format "#;#;#;N/A" if you don't want to show nulls as Blank.

CodePudding user response：

Read up Dsum function in ms access, its the closest to sumif of excel