Calculating fields on a Form

[Franklin]

I have created a Call Monitoring Database, but I need help with the VB code
in order to obtain a total score.

There are 22 questions on the form. (For the sake of this post, let’s say
they’re labeled as Question1, Question2, Question3, etc.)

Each question field is set to “Number†as the data type, with Combo Box
display control, Value List row source type and the row source set to
0;1;2;3;4

Therefore, users will have the option of choosing either 0,1,2,3 or 4 as an
answer choice for each question. (There is a legend provided on the form to
elaborate on the number values).

I also have one field on the bottom of the form (Total Score) with a
“Number†data type, field size: long integer, format: percent.

I need a VB code that will run the following:

To Calculate the Total Score as a Percent:

1. Count the number of Questions with any value other than 0
2. Multiply the count in step #1 by 4
3. Divide the total score by the number obtained in step 2
4. Convert the result in step 4 to a whole number percent

For example, let's say 20 out of the 22 question are answered with a value
other
than 0. Let’s say these 20 questions all had a value of 3.

1. 20 (number of items with a value other than 0)
2. 20 (count from step #1) x 4 = 80
3. 3 (value) x 20 (# of questions with a value other than 0) = 60 (total
score)
60 / 80 (number obtained in step 2) = 0.75
4. 0.75 x 100 = 75%


I’m aware that the database may not be normalized.

I'm very new to this so any help with the VB code, or any other means to
calculate this, would be greatly appreciated. Thank you in advance!

--Frank

 

[John W. Vinson]

I have created a Call Monitoring Database, but I need help with the VB code
in order to obtain a total score.

There are 22 questions on the form. (For the sake of this post, let’s say
they’re labeled as Question1, Question2, Question3, etc.)

I fear you've fallen into the "fields as data" trap that's all too common.

What is the structure of your Table (remember, tables are fundamental, forms
are just tools to manage data in tables)? Do you have 22 question fields?

If so, what will you do when you decide you need to delete Question 6 and add
two more questions? Restructure your table, your form, your reports, all your
queries, all your calculations? OUCH!

Or are your business rules such that these 22 questions are the ONLY questions
that will *ever* for the life of the business (or at least the life of the
computer application) ever be relevant?

A MUCH better design is to have three tables: Calls; Questions, a 22 row
(currently) table with one question per row; and Answers, related one to many
to each of the other two tables. You can use a continuous Subform on a Calls
form to enter the answers to the questions. You can then use a very simple
Totals query to sum the answers for a given Calls record.

Each question field is set to “Number” as the data type, with Combo Box
display control, Value List row source type and the row source set to
0;1;2;3;4

Therefore, users will have the option of choosing either 0,1,2,3 or 4 as an
answer choice for each question. (There is a legend provided on the form to
elaborate on the number values).

I also have one field on the bottom of the form (Total Score) with a
“Number” data type, field size: long integer, format: percent.

I need a VB code that will run the following:

To Calculate the Total Score as a Percent:

1. Count the number of Questions with any value other than 0
2. Multiply the count in step #1 by 4
3. Divide the total score by the number obtained in step 2
4. Convert the result in step 4 to a whole number percent

For example, let's say 20 out of the 22 question are answered with a value
other
than 0. Let’s say these 20 questions all had a value of 3.

1. 20 (number of items with a value other than 0)
2. 20 (count from step #1) x 4 = 80
3. 3 (value) x 20 (# of questions with a value other than 0) = 60 (total
score)
60 / 80 (number obtained in step 2) = 0.75
4. 0.75 x 100 = 75%


I’m aware that the database may not be normalized.

I'm very new to this so any help with the VB code, or any other means to
calculate this, would be greatly appreciated. Thank you in advance!

--Frank

If you feel that you're stuck with the non-normalized design you will need
either some VBA code or a very large hairy multiple IIF statement to do this
calculation. I'm not sure that the statement would even be allowed, it might
exceed the size limitation on Control Source expressions.

 

[Mike Painter]

I'm aware that the database may not be normalized.

I'm very new to this so any help with the VB code, or any other means
to calculate this, would be greatly appreciated. Thank you in advance!

Normalize it.
It will make things a lot easier and allow you to add questions down the
road. Most computer tests today present a random order of questions with
random questions coming from a bank of questions. These can be grouped so
all the areas are covered but people will tend to get different questions
tht cover the same subject.

Incidentally there are a ton of such applications on the web and many of
them are low or no cost.