Two approaches:-
1) (my preference) Redesign your table structure to have a second
relationally linked table just for responses. The structure of this
table will be:
fldPKey Primary Key (I always use Autonumber PKey fields)
fldEmpNo Employee No (links to Employee Table)
fldQuestNo Question No (may link to table with text of the
question)
fdlResponse Response!
Your Data entry form will need to use one or two subforms (probably
two parallel columns for Q1-20 and Q21-40) to enter the responses.
fldResponse will be an Integer field. Use -1 for Satisfactory, 0 for
Not Satisfactory, and Null for Not Applicable. You can either use
"three state" tick boxes or bound listboxes displaying your current
choices.on the forms to enter the data. In the Query (or Report) that
displays the answers, it is trivial to sort by employee and/or
question and to count Satisfactories, almost as trivial to count
Nulls, and the difference between the total of the first two and the
total records is Not Satisfactories.
2) (not recommended!) Whenever you need to count results, add columns
in a Query of the form "IIF(txtAnswer1 = "S", 1, 0) | IIF (txtAnswer1
= "N", 1, 0). Here, the difference between the sum of ones in these
two columns and the total count is the "not applicables" for this
question.
I have created a table, report & form with 40 questions, each questions has a
lookup field with 3 possible answers (satisfactory, unsatisfactory & not
applicable). I need to total how many times each employee used each answer.
Example: Total: Satisfactory (5) Unsatisfactory (3) Not Applicable (1).
Any help would be greatly appreciated
Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
