Calculating an average

[bhrosey]

I have a form that has a list of training tasks with a check box beside each
one. I want to calculate an average score of completed tasks on the form
itself, so that whenever an employee has completed a new task the score will
update. It's a set number of tasks that will never change. Thanks!

 

[John W. Vinson]

I have a form that has a list of training tasks with a check box beside each
one. I want to calculate an average score of completed tasks on the form
itself, so that whenever an employee has completed a new task the score will
update. It's a set number of tasks that will never change. Thanks!

What's the structure of your Tables? (Forms don't contain data). What's the
Recordsource for the form? Where (if at all) do you want to store the score?

John W. Vinson [MVP]

 

[bhrosey via AccessMonster.com]

John, I have a table with a generic autonumber, the employee's name, hire
date and a field for each task (set up as a yes/no). The score can saved in
this table as well.

 

[John W. Vinson]

John, I have a table with a generic autonumber, the employee's name, hire
date and a field for each task (set up as a yes/no). The score can saved in
this table as well.

Then your table structure *is wrong*.

What will you do when a new task is added? Redesign your table, redesign your
form, rewrite all your queries, recalculate all your scores (because the base
list of tasks is changed)? OUCH!

A many (employees) to many (tasks) database needs THREE tables: Employees;
Tasks; and a third table with EmployeeID and TaskID with one record for each
task accomplished. The score would be calculated and displayed dynamically
using a totals query, and need not and should not be stored anywhere.

John W. Vinson [MVP]

 

[bhrosey via AccessMonster.com]

John, the list of tasks will never change. It's a list of things all new
hires have to learn. All we'll do is check a box when the employee has
learned that task, so I need to keep a running average. Whenever I look up
that employee, I want to be able to see how far along they are in their
training.

 

[John W. Vinson]

John, the list of tasks will never change. It's a list of things all new
hires have to learn. All we'll do is check a box when the employee has
learned that task, so I need to keep a running average. Whenever I look up
that employee, I want to be able to see how far along they are in their
training.

You can't record WHEN an employee learned the task in a checkbox. I'd argue
that my advice stands. Do you in fact have sixteen checkboxes AND 16 Date/Time
fields? Do you also have a score field in the table? Just how is the score
calculated?

John W. Vinson [MVP]

 

[bhrosey via AccessMonster.com]

John, I don't care WHEN they learned the task, just that they learned it. I
don't KNOW how to calculate it, that's what I'm trying to find out. What I
was told was... score:abs([field1],[field2],field3])/3, but whenever I try it
I get a "Name?" error.

 

[John W. Vinson]

John, I don't care WHEN they learned the task, just that they learned it. I
don't KNOW how to calculate it, that's what I'm trying to find out. What I
was told was... score:abs([field1],[field2],field3])/3, but whenever I try it
I get a "Name?" error.

Thank you. You *didn't say that*, and my telepathy was not working.

Again, let me ask, so I'll know whether or not I'm answering the right
question:

How do you want to calculate the score?

You have 16 yes/no fields. What is the score if all 16 are answered Yes? if
all are answered No? Does the score depend on which questions get answered yes
and which get answered no, or just on the total count?

John W. Vinson [MVP]

 

[bhrosey via AccessMonster.com]

John, I don't care WHEN they learned the task, just that they learned it. I
don't KNOW how to calculate it, that's what I'm trying to find out. What I
was told was... score:abs([field1],[field2],field3])/3, but whenever I try it
I get a "Name?" error.

Thank you. You *didn't say that*, and my telepathy was not working.

Again, let me ask, so I'll know whether or not I'm answering the right
question:

How do you want to calculate the score?

You have 16 yes/no fields. What is the score if all 16 are answered Yes? if
all are answered No? Does the score depend on which questions get answered yes
and which get answered no, or just on the total count?

John W. Vinson [MVP]

If all are checked "yes", it would be 100%. 12 of the 16...75% and so on.
It doesn't matter which ones get checked. Thanks for the help!!

 

[John W. Vinson]

If all are checked "yes", it would be 100%. 12 of the 16...75% and so on.
It doesn't matter which ones get checked. Thanks for the help!!

Ok... you can then set the Score textbox's Control Source to

= - ([Field1] + [Field2] + [Field3] + ... + [Field16]) / 16

This works because a Yes/No field (checkbox) stores -1 for Yes, 0 for No. You
can sum all the yes/no fields; a perfect score would be -16, half right would
be -8 and so on. Take the negative of this and divide by 16 to get the
average.

John W. Vinson [MVP]

 

[bhrosey via AccessMonster.com]

If all are checked "yes", it would be 100%. 12 of the 16...75% and so on.
It doesn't matter which ones get checked. Thanks for the help!!

Ok... you can then set the Score textbox's Control Source to

= - ([Field1] + [Field2] + [Field3] + ... + [Field16]) / 16

This works because a Yes/No field (checkbox) stores -1 for Yes, 0 for No. You
can sum all the yes/no fields; a perfect score would be -16, half right would
be -8 and so on. Take the negative of this and divide by 16 to get the
average.

John W. Vinson [MVP]

That worked axactly as I wanted, Thank you, very, very much!!