Query Option Button Data - Help! 1

[marksprech]

I am trying to write a query that will take the data returned to my table by an option button control on a form and convert that data to meaningful information. The control has 4 option buttons which naturally return values of 1, 2, 3, and 4 respectively. The buttons are selected by evaluators who are rating the quality of employee's work: 1-satifactory, 2-needs improvement, 3-unsatisfactory, 4-not rated. I need to count all the 1's, 2's, and 3's for a total and assign values of 1 = 1, 2 = .5, and 3 = 0. Then I need to divide the count of 1's only by the total count of 1 through 3 (4 is irrelevant for this operation)so I can produce a rating by employee. I am beginning to think I am brain dead from struggling with this.

 

[sko]

Since you say you are counting the occurances of a particular grade and dividing by the count of all the grades, I'm not sure where you are using the values (1=1;2=5;3=0). Please show some sample data for an employee w/the rating.

Assuming you are storing the evaluations in a table similar to this.

tblEvaluations
Recordnum
EmployeeID
Grade

Sample data for tblEvaluations
Record1 Employee1 1
Record2 Employee1 1
Record3 Employee1 2
Record4 Employee1 3
Record5 Employee2 3
etc.

 

[marksprech]

I apologize for the error in the question. I do not need to divide the count of the one's by the total count of 1 thru three. Let me say it correctly this time. The form sends the evaluator's option button result to a table that holds several different categories on which an employee is rated. Each category is rated and the value returned to the table is 1 thru 4 as dictated by the option button selected. Each employee will have a grade developed for each category (evaluators will look at several of an employee's files and rate each of them). For example, an employee might be have 10 files evaluated with counts as follows:

Satifactory Needs Imp Unsat Not Rated

Service 6 1 2 1

The value in the table for each Stisfactory is one (1), for each Needs Imp is two (2), for Unsat is three (3), and for Not Rated is four (4) as dictated by the option buttons. I need the query to convert the two's for Needs Imp to point five's (.5) and the three's from Unsat to zero's (0). In the above example the total number result should be 6.5 which would then be divided by the total receiving a rating (nine) yielding a grade for that category of 72.22%. I would query each category and then produce a report by employee and another by supervisor (combining the appropriate employees).

Thanks for your help.

 

[sko]

Okay, here are my tables:

tblGrades
Grade (pk)
Description
Value

Data:
1 Satisfactory 1
2 Needs Imp .5
3 Unsat 0
4 Not Rated ?


tblEvaluations
EmployeeID
Category
Grade

Data Sample:

Emp1 Service 1
Emp1 Service 1
Emp1 Service 1
Emp1 Service 1
Emp1 Service 2
Emp1 Service 3
Emp1 Service 3
etc.

tblEmployee
EmpID (pk)
Name
etc.


Bring all tables into query
Join tblEmployees.employeeid to tblEvaluations.employeeid
Join tblGrades.grade to tblEvaluations.grade

Show the Total line in the query grid

Query columns (I've added the value field twice)

tblEmployees.employeeid
tblEmployees.name
tblEvaluations.category

tblGrades.value (change group by to sum)
tblGrades.value (change group by to avg, format %)

Run query


HTH