Questionaire Form requires 3 possible answer choices per question 1

[neobyte]

I'm building a series of forms for use in an audit. There are as many as 70 "questions" to be answered on an individual form, the answer choices are "Yes, No, or N/A", and there will be 7 or more forms. The questions are not interchangeable on the different forms. Each form is based on its own table/query, no calls to other tables. (If I had enough time I'd build a giant table with all the questions, then call them to their respective forms, but it wouldn't solve the immediate issue)

The questions MUST be answered using button controls/check boxes, no combo/lists allowed. (hey, the customer pays for the privilege, any arguments for something simpler are a waste of time) Among other requirements is for one or more big-wigs to be able to "review" the completed audits to see the "Yes, No, N/A" selections. (It's likely some other fool will want to see printed versions of them as well, but I'll wait to get the input working before hoisting myself on that petard)

There are 10 sections, and the questions in each section are to be answered and the section scored, with ratings based on the number of Yes vs No after deducting the number of N/A - i.e 5 "Yes", 3 "No" 2 "N/A" = 5/8 *100 = 62.5% - only 8 of 10 questions are "applicable" so the score is 8 answers - 5 "Yes" to 3 "No" = "5 out of 8 right".

A final score will report overall results as a percentage. It is possible that an entire section is marked N/A. The final score will be calculated by totaling the section scores, dividing by (total number of sections - number of sections N/A), the same basic process as that for each section.

One way to control this is a simple but very lengthy process; create 3 answer fields for each question and apply appropriate calculations. Which results in 210 + fields for the biggest.

Anyone have any suggestions as to how to use a 3-option group control for a single field for each "answer" to achieve something that doesn't fall prey to the "subtract the wrong answers from the right" while retaining the N/A value?

So far, all efforts using 3-button groups and a variety of event procedures to assign values other than -1, 0, 1 have failed to produce a reliable result.

 

[GingerR]

Hi. Seems kinda simple, unless I'm missing something? Have one field in the table named i.e. "Response". Have an option box on the form with three choices, and the control source of the option box is Response. You can make the option buttons = anything you want. Translate them later if you need to, using a table that defines what each one means/what their value is, for calculations and such.

Not sure what you mean by "doesn't fall prey to the 'subtract wrong answers from the right'?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[neobyte]

GinerR;

I guess I shouldn't open the Scotch until AFTER I write the cry for help.

I think this is one of those annoying situations where there is no "shortcut" - I more or less have to duplicate a spreadsheet layout on an Access form. I need 3 "answer boxes" (option controls)for each question, I need a count of Yes, a count of No and a count of N/A for a "list" of questions, then I have to calculate the count of the Yes against the number of "asked" questions minus the "Non/Applicable" questions. (It's almost as if there are 2 "right" answers for each question)

I just didn't feel like going through about 1,187 field definitions to make the thing work.

I need another drink anyway, so this is as good a reason as ever. The whole thing makes sense when you look at a diagram but describing it is another thing altogether.

(Worse than all this is the fact that I can't see any useful purpose in asking "Not Applicable" questions in some sort of audit process. If it's not applicable, who gives a $%&!*$ $(~^? Maybe that's why I ain't a CFO)

 

[GingerR]

So you have to put the questions out horizontally? You don't have your questions each hardcoded as a field name do you?!?! Why do you have to lay it out like a spreadsheet? I highly suggest you don't!!!

Similar to your situation, I recently built a survey with option buttons, default = 0 (NA) then satisfaction from LOW to HI (radio buttons, values = 1,2,3,4)

The tables were normalized, and questions loaded for each user as they opened the db. So data set up like this:

tblGroup
Field: GroupID
Field: GroupName

tblQuestions
Field: GroupID
Field: QuestionID
Field: Question

tblPeople
Field: UserID
Field: UserName

tblPeopleQuestions
Field: UserID
Field: QuestionID
Field: Response (value 0-4 based on option box selection)

Main form with user's name, close button, etc.
Subform which showed one GROUP'S questions per record. 9 groups, differing number of questions per group. Nav thru subform records with nav buttons. Each group listed at top of each subform, with questions and place for user to choose response, as well as add comments. Looked sorta like this:

Tom Smith ______________________________________________________
Group 1 NA LO -- HI COMMENTS

Question 1 o o o o o
Question 2 o o o o o
Question 3 o o o o o
Question 4 o o o o o
Question 5 o o o o o
______________________________________________________

Obviously it's more complicated that this, but the table structure is normalized, questions go DOWN not across. No issues with capturing the value of the option box, or doing any calculations after the survey was done.

I built a great report which shows avg response for each question (actually it's engineers rating the processes they have to use). I showed the number of people rating each item. This told us quickly and easily what processes suck the most for the most number of people.

Once you have your tables set up properly, all calculations are a breeze!



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[neobyte]

GingR

Just goes to show, I can't describe the thing worth a damn. Yes, what you did is exactly what I'd like to be able to do, but the instructions I have to follow are something else. The thing they want looks more like a spreadsheet than a data form.

My questions are vertical. (Some of the damn things are 30 word phrases, having multiple "subquestions". Hardcode the questions as field names - nope, didn't think of that for more than 8 seconds - about the time it took to the first drink to go down.)

So I've got something like 500-600 "questions". The largest single questionaire is 70, divided into 10 sections, but not in equal numbers of questions per section. Each section MUST show percentage scoring, then a user "comments" and a user "grading" (I can combo this thing at least)

Where it crashed through the thin membranes that protect the vaccuum between my ears is having to score the sections by excluding the N/A.

Looking at your model & checking a range of responses

Group 1 NA LO -- HI COMMENTS

Question 1 X o o o o
Question 2 o X o o o
Question 3 o o X o o
Question 4 o o o X o
Question 5 o o o o X


Based on your 0-4 "satisfaction" index, my sample would "score" 10, ... if this suited what I have to do, it would be 10 out of 20, or 50% ... but I have to take the 5 questions, throw out the N/A (and it's potential score max of 4) and return a value of 10/16, or 62.5%. And I have to put that score immediately below the last question in the section. In my model, if you answer 1 "Yes" & 4 "N/A", you score 100% - in fact, if you answer all 5 N/A, you score 100% - apparently knowing something is N/A is a good thing.

The "scorebox" for you would be Q1+Q2+Q3+Q4+Q5/5. For me, it's Q1+Q2+Q3+Q4+Q5/[5-(anyQ=N/A)]. Now, since the responses go into a single field for each Q in your model, what the hell magic do I do to get that/those elusive "N/A" out of the 5 question sequence as a "count", so I can somehow subtract it from the 5 count to give me the number of questions actually answered. For whatever reasons, these guys insist that the "N/A"'s be subtracted from the question count. Whatever I've tried so far with 3-control option groups either subtracts the wrongs from the rights, adds the N/A to the count, or gives me a "percentage" that is so far out of whack no one would believe it. (Or just throws an #Error)

(I tried one oddball offset using a separate single option button that more or less does nothing but show you selected "No", but I have to write 2 events for each one of these to prevent a situation where someone selects Yes, then changes their mind and 2 buttons remain pressed in - that might cause one or more senior exec's to leap from the roof. 1,200 procedures is a bit of pain)

And all of the stuff MUST appear on the form/subform - the Access input form is the report (or least I gotta make it so that's what comes out when you push that button, along with various "summary reports" that have to go out to spreadsheets)

And to make matters even more annoying, none of the parent form data, another 11 fields, is preloaded into tables for selection. The same people will use it repeatedly, but they will screw up their names (no matter how big you make the "LastName FirstName" tag, or even give them separate fields - lots of folks out there with the prefix Mr/Ms in their names)All names, numbers etc are going to be input by the users (which means any sort of relational analysis based on that part will be pretty much useless, but they refused any suggestions about that part, too)

I'm not allowed to indicate anything that might identify who/what/where/ this thing is being used. But there's legal experts involved in it, so there's no hope for respite in having anything changed to simplify the requirements.

Best bet; I'm just to thin between the ears to see that your model works perfectly in this case.

Much thanks for your kindness.

(I think this thing will be used to find out who sucks at their jobs so they can fire them - glad I'm not an employee. Then again, maybe they are, too)