Help with VB code

[Franklin]

I'm new to the forum, so I apologize if I am posting this in the wrong section.

Here's my situation:

I have created a Call Monitoring Database, but I need help with the VB code
in order to obtain some figures. There are 22 questions on the form, each
with a combo box that contains the values: 0,1,2,3,4

I need a VB code that will run the following:

To Calculate the % Rating:
1. Count the number of items 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 of the 22 question are answered with a value other
than 0. For this example, let's say all 20 questions 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 very new to this so any help would be greatly appreciated. Thank you in
advance!

--Frank

 

[Stefan Hoffmann]

hi Franklin,

I have created a Call Monitoring Database, but I need help with the VB code
in order to obtain some figures. There are 22 questions on the form, each
with a combo box that contains the values: 0,1,2,3,4

Normally your table layout should look like this:

Question: ID, QuestionText
Participant: ID, ParticipenttName
Answer: idParticipant, idQuestion, Answer

And this displayed using a continuous form.

To Calculate the % Rating:
1. Count the number of items 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

SELECT idParticipant, Sum(Answer) / (Count(*) * 4) AS Score
FROM Answer
GROUP BY idParticipant
WHERE Answer > 0

or

SELECT Sum(Answer) / (Count(*) * 4) AS TotalScore
FROM Answer
WHERE Answer > 0


mfG
--> stefan <--

 

[Franklin]

Hi Stefan,

The questions are laid out on my form as follows:

Question 1 - (Combo Box, 0,1,2,3,4)
Question 2 - (Combo Box, 0,1,2,3,4)
etc, etc
Question 22 - (Combo Box, 0,1,2,3,4)

At the bottom of the form, I have created a "Final % Score" field. The data
type is set to Number, Field Size: Double, Format: Percent.

Where would I insert my field names in the code in order to have the final
score appear on the form in the "Final % Score" field.

Thanks again for the prompt response.

 

[Stefan Hoffmann]

hi Frranklin,

The questions are laid out on my form as follows:

Question 1 - (Combo Box, 0,1,2,3,4)
Question 2 - (Combo Box, 0,1,2,3,4)
etc, etc
Question 22 - (Combo Box, 0,1,2,3,4)

This is called a not normalized table. See:

http://en.wikipedia.org/wiki/Database_normalization

You should try my solution.

Where would I insert my field names in the code in order to have the final
score appear on the form in the "Final % Score" field.

Simply create a control source for your final score field in the
property editor like this

(cmbQ1.Value + ... + cmbQ22.Value) / (-4 * (
((cmbQ1.Value>0) + ... + (cmbQ22.Value>0)))


mfG
--> stefan <--

 

[Franklin]

Stefan,

I apologize for not being thorough, but I didn't mean to imply those were
the only fields on my form. I have a normalized table. I have completed
everything that needs to be set up (tables, primary key, relationships, etc).

I'll try your solution and let you know if it works. Thanks again!

--Franklin

 

[Franklin]

Stefan, it didn't seem to work. The error message stated "text is too long to
edit."

Help!

--Frank

 

[John W. Vinson]

Stefan,

I apologize for not being thorough, but I didn't mean to imply those were
the only fields on my form. I have a normalized table. I have completed
everything that needs to be set up (tables, primary key, relationships, etc).

If you have 22 fields for your 22 questions, I must beg to differ on your
assertion that the database is normalized.

What will you do when you need to add questions 23 and 24, and delete question
8? Redesign your table, rebuild all your queries, redesign your form, rewrite
your code, redesign all your reports? I fear that's the corner you're painting
yourself into!

Tall-thin tables, as Stefan suggests, are MUCH more flexible and adaptable.

 

[Stefan Hoffmann]

hi Franklin,

Stefan, it didn't seem to work. The error message stated "text is too long to
edit."

What exactly does not work?



mfG
--> stefan <--