Trying to create an audit database inc calculations

[Ellen07886]

Hi all

I'm hoping some of the experts out there might be able to help me out. I've
been asked to develop an audit database.

I've got all the questions in, with scores of 0 (non compliance), 5 (partial
compliance) and 10 (full compliance). Reports will calculate the scores
without any problems however I need to add the following.

1) include a 'not applicable' category in the score field

2) A percentage figure for the questions asked (for example 10 questions
give a 10 (full compliance) would produce 100%) This is easily calculated.
BUT

3) We need to deduct the number 'not applicable' answers BUT still produce
a % figure ie 10 questions 5 score 10 (full compliance) and 5 n/a therefore
the % score would be 100%. There can't be any % reduction for n/a answers.

Does anyone out know how I could go about this?

 

[GBA]

make your N/A a separate field. you can't put the text letters of N/A into a
number field anyways in terms of those ratings.

perhaps a check box field would be nice.

then when you do your totalling...you need a query that filters out the N/As
so that the summing only involves those with number values.... source the
report or subreport on this query.

 

[KARL DEWEY]

Do not use 0, 5, and 10.
You can use an Option Group on your form with 0 through 3 for
non-compliance, partial compliance, full compliance, and N/A.

Zero is non-compliance and 1 is partial compliance. Then greater than 1
can score as full compliance.

 

[James A. Fortune]

Hi all

I'm hoping some of the experts out there might be able to help me out. I've
been asked to develop an audit database.

I've got all the questions in, with scores of 0 (non compliance), 5 (partial
compliance) and 10 (full compliance). Reports will calculate the scores
without any problems however I need to add the following.

1) include a 'not applicable' category in the score field

2) A percentage figure for the questions asked (for example 10 questions
give a 10 (full compliance) would produce 100%) This is easily calculated.
BUT

3) We need to deduct the number 'not applicable' answers BUT still produce
a % figure ie 10 questions 5 score 10 (full compliance) and 5 n/a therefore
the % score would be 100%. There can't be any % reduction for n/a answers.

Does anyone out know how I could go about this?

Consider using a Null value to indicate N/A. Some SQL aggregate
functions, such as Avg() and Sum() ignore Null values already. COUNT(*)
doesn't ignore Null's, but SUM(IIF(MyField IS NULL, 0, 1)) or even
Count(MyField & MyField) does. That leaves the percentage calculation.
How about:

SELECT 10 AS FullCompliance, Sum(Score)*100/(Count(Score & Score) *
FullCompliance) AS Percentage FROM tblComplianceScores;

or (rearranged to remove some parentheses)

SELECT 10 AS FullCompliance, Sum(Score)*100/FullCompliance/Count(Score &
Score) AS Percentage FROM tblComplianceScores;

or (with percentages rounded)

SELECT 10 AS FullCompliance,
Round(Sum(Score)*100/FullCompliance/Count(Score & Score), 2) AS
Percentage FROM tblComplianceScores;

That seems to work even if there are no scores or all 0 scores or no
records in the table at all.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Consider using a Null value to indicate N/A. Some SQL aggregate
functions, such as Avg() and Sum() ignore Null values already. COUNT(*)
doesn't ignore Null's, but SUM(IIF(MyField IS NULL, 0, 1)) or even
Count(MyField & MyField) does. That leaves the percentage calculation.
How about:

SELECT 10 AS FullCompliance, Sum(Score)*100/(Count(Score & Score) *
FullCompliance) AS Percentage FROM tblComplianceScores;

or (rearranged to remove some parentheses)

SELECT 10 AS FullCompliance, Sum(Score)*100/FullCompliance/Count(Score &
Score) AS Percentage FROM tblComplianceScores;

or (with percentages rounded)

SELECT 10 AS FullCompliance,
Round(Sum(Score)*100/FullCompliance/Count(Score & Score), 2) AS
Percentage FROM tblComplianceScores;

That seems to work even if there are no scores or all 0 scores or no
records in the table at all.

James A. Fortune
(e-mail address removed)

As John Spencer noted elsewhere, Count(Score) should work the same as
Count(Score & Score).

JAF
(e-mail address removed)