Evaluation Forms in Access with Formulas

[zyousafi]

I am creating a Database in Access for a Contact Center. A big part of Contact Centers is Quality Control Evaluations. I am having some trouble creating a form where the evaluation would take place and as it is taking place maintain a field that would automatically calculate the score and then eventually store that final score in a field in the "Master" Database. The main problem is the formula.

 

[apr pillai]

Take a look at this example where the User can enter Custom Formula with normal Arithmetic Operators/Parenthsis etc. and calculate the result.

http://www.msaccesstips.com/2008/11/custom-calculator-and-eval-function/

You can use an Unbound Textbox to enter the Expression to calculate the result on a Button Click or Lost Focus Event Procedure.

Example: me![target] = Eval([UnboundTextBoxName])

will calculate the result of the expression and will store it in the Field with the name Target on the Form.

 

[jzwp22]

Welcome to AWF!

First, for databases, you generally don't store a calculated value. You only store the data that is used to calculate it. This helps to preserve data integrity.

In order to help you further, we will need more information as to what you are doing. What fields hold the raw data and what is the calculation?

 

[zyousafi]

Thank you very much for the warm welcome and replying. I have went through the attached link and I think I need to provide more details as to what I am actually doing.

I have created this template (the template is attached) that will help you better understand what I am trying to do. The user will have the "QCMS Dashboard" in front of him/her when he/she is about to conduct an evaluation. The dashboard will contain many options; however in this example I have only included one command button. When he/she is ready to conduct an evaluation then he/she will press the "New Evaluation" command button and the "Evaluation" form will come up. This form will contain all of the necessary quality parameters with a simple dropdown that allows the user to select "Yes", "No", or "Not Applicable". The actual form will contain several quality parameters; however I have only included four in this example. "Greeting" = 2 Points, "Objective" = 6 Points, and "Closing" = 2 Points. "Fatal Error" = -5 Points when "Yes" and 0 Points when "No". The "Evaluation Score" field should, as the user is conducting the evaluation, be calculating the overall score. Once the "Submit" command button is pressed, all of the data should be sent to the "Evaluation" table and the "Evaluation" form should close.

I am currently working on several different aspects of QCMS; however this has been the biggest challenge I have run into so far. I am not an expert by any means. I have simply learned the basics of Access just by creating small templates like the one attached for my teams. I hope the attached template will help you understand what I am trying to accomplish.

 

[jzwp22]

The key to any successful relational database is the table structure. Everything else is secondary. The quality parameters must not be fields in a table like you have them now, but rather records in a table. If you use your current approach and have to add additional quality parameters, then you will have to redesign your table and all associated forms, queries, reports, macro etc.

Based on your brief description, the following is a possible table structure:

tblQualityParameters
-pkQualityParameterID primary key, autonumber
-txtQualityParameter
-longQualityPoints

You then need a table to hold the basic information about the evaluation (who, when, where...)

tblEvaluationInfo
-pkEvalInfoID primary key, autonumber
-fkPeopleID (foreign key to a table holding the information about the person doing the evaluation)
-dteEval date of the evaluation


Now you need a table that joins the specific quality parameters to the evaluation

tblEvaluationQualityParameters
-pkEvalQParametersID primary key, autonumber
-fkEvalInfoID foreign key to tblEvaluationInfo
-fkQualityParameterID foreign key to tblQualityParameters
-fkResponseID foreign key to tblResponses

tblResponses (3 records: yes, no, not applicable)
-pkResponseID primary key, autonumber
-txtResponse

There is a key concept involved in designing the table structure. It is called normalization. This link gives some details on normalization and why it is important.

 

[zyousafi]

jzwp22, thank you for the detailed reply. I have actually spent the past couple of days trying to do what you suggested; however I fail to see how that will allow me to accomplish what I am aiming to accomplish. I am sure there is something that I missed or I simply do not understand how to do what you have said.

With that being said, the main issue I am currently facing is the formula. I want to formula in the form and not in the query at this current moment. I have tried putting the formula in the Expression Builder but I have had no success.

Can you please help me on that front?

 

[jzwp22]

I've attached an example database using the structure I proposed that calculates the points. Check out the form: frmEvaluationInfo. Since the points are awarded when a response is provided, I put the expression in the After Update event of the response combo box. That same code needs to be repeated in the On current event of the subform also to do the calculation for existing records. The result of the calculation is not stored in a table, it can be generated whenever you need it.

I did not know what points to assign for a Not Applicable response, so I assumed that it would be 0 just as the response for No.

 

[zyousafi]

Thank you very much jzwp22. You have been a great help!

 

[jzwp22]

You're welcome; glad I could be of some assistance. Good luck with your project!