 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2007Calculating an average
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! >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] 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, 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] 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, 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] 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, 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] >>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 [quoted text clipped - 13 lines] > > 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!! >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] >>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!! [quoted text clipped - 9 lines] > > John W. Vinson [MVP] That worked axactly as I wanted, Thank you, very, very much!! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.