Access 2007 Track Answers to a List of Questions

I am trying to determine the best way to capture testers answers to 35 questions. For some reason my brain is not putting this together right. I have put the 35 questions into a separate table and created another table with the testers info, but I can't seem to get them to function properly. I have given table 1 the primary key and used table 1 primary key in table 2 as a foreign key. I'm thinking maybe I should've put each question as a separate field, versus creating the question field and populating the table with the questions, but nothing seems to be coming together right. I've done quite a few databases, but I've never had to capture results to the list of questions so that the tester can be scored. What am I not doing right? I am obviously on the wrong track. I appreciate any help in pointing me in the right direction.

Rhonda, I have created a few questionnaires in Access using different approaches.

One approach is to store all of the questions in a Source table and then use an Update query to change the user name. Next I use an Append query to add the questions to the Destination table. A typical workflow would be to change "Bob" to "Mary" in the Source table. Next the application copies all 35 questions with the name Mary as the responder to the Destination table. The queries can be executed through either VBA or macros. Start by testing each query separately and then combine them into one VBA module or macro.

You can add fields to the Source and Destination tables to store scores and weighting factors, if you use them. Weighting factors can be 1, 2, 3, 4, and 5 or they can be 1, 3, 5, 7, and 10 if you want clearer definition between good/bad and go/no go questionnaires.

Alternatively, you could use Access' Option Groups. If you do this then multiply the Frames values by your weighting factors.

I usually copy the values (score times weighting factor) into a separate Textbox that uses conditional formatting or if-Then-Else or Select Case statements for color coding. I use green for excellent (5), yellow (3) for OK, and red (0) for bad. Occasionally. I use blue (4) and black (1, for poor or unknown).

If the responses are simple yes/no then you could use a multivalue field. The responses in this situation would be stored in a lookup table. Multivalue fields won't let you do the math, at least I can't think of how to do it with them.

The method you use depends on the type of questionnaire that you need. Before you start building the questionnaire, think through these methods and select the one that best meets your requirements now and in the future.

You should also decide up front which is better for your questionnaire, Access or Excel. If your users do not have Access on their PCs then Excel could be a better choice. I prefer Access myself but the choice is yours.

I forgot to mention that with my first approach you will need a testers table to feed data to your main form. Your questions will appear in a subform. You also need a form for editing the questions.

Your questions table may have fields for the tester name, questions, answers, scores, and weightings depending on how you set up the questionnaire. The Source and Destination tables should have the same fields.

Based on your post, it seems that you may need to do a little more analysis, design and development.

What happens if the questions change over time or based on test version? what type of questions are being asked? Yes/No, one of multiple choice, multiple multiple choice, short text or essay? How to you plan to relate test, tester, question and answer? Does the application calculate the score and store the score? All of these should be known before you even think about table design and relationships. Do not provide the answers to these questions as we are not here to do your work.

But to answer your specific inquiry is a MTM (many-to-many) relationship table - rec_id, tester_id, question_id is what you are looking for. Or you can just use tester_id and question_id and use both as the unique key. This can be expanded to include either the answer or a relationship (answer_id) to an table of available answers if the questions are multiple choice. You can figure out how to set up the question / answer tables, relationships and question forms.

Thank you all for your interest. I may need to rethink the design, but I'm not sure. These are my requirements:

The tester will input their name and etc, which will assign a unique tester_id to the user.
Then they will proceed to answer the 35 questions (not likely to change), choosing one of 5 options (Strongly Agree, Agree, Undecided, Disagree, Stronly Disagree). Each option is weighted individually dependent upon the question. Each question falls into one of three categories, which is how the tester is scored. The tester information will need to be retained in order to measure the results.

When I tried using a continuous subform, all the questions from the table are available, but when I tried to create an option group for the user to use to select their answer, it populated for all the questions.

Should each question be a separate field and then the tester table have an individual field for each question?

Many years ago the first Windows app I created was a survey tool. It sounds like, from your description, you have the same objective. I found and converted the database and pulled a screen print of the table relationships I used. I'm happy to share it. But first, here's a description of it and the functions performed.

To set context, my paradigms point to a "tester" as one who administers a test while a respondent is the one taking the test. It seems that my respondent is your tester.

1. Has there been any consideration for administrative functions? If not, and if there's the need, another table might be needed. These would be for administrators and their passwords.
2. Here are the tables I used along with their relationships.

Surveys Table - primary table
1 to many relationships with a Questions Table and a Respondents Table

Questions (Statements) Table
Many to 1 relationship with Surveys on Survey Id
Many to Many relationships to a Responses Table on Survey Id
1 to many relationships to a Responses Table on Question No.

Respondents Table
Many to 1 relationship with Survey Table on Survey Id
Many to many relationship to a Responses Table on Survey Id
One to many relationship to Responses on Respondent Id

Responses Table
This table's relationships are listed above.

4. In reading the possible responses I have some thoughts. Here they are.

I see the Undecided but I personally find that there are always questions or statements that are not applicable. When I take a survey and I come to one of those and I don't have a N/A response, I declare victory and exit the survey if some sort of response is required. I won't enter a response that's not accurate.

One of the useful aspects of a survey, and I rarely see it is an assignment of Importance to the respondents response. For example, when analyzing the data, if there's a "Strongly Disagree" that is "Not Important" I'm going to spend less time evaluating that response than I would if it were "Very Important." All the wonderfulness of the collected data is as useful as a screen door on a submarine if it can't be actually used to identify or solve some business problem. When the data or post project review, assuming that all participants are in the room, occurs, time spent on unimportant responses can serve to lable such reviews as a "big waste of time."

One of the more useful aspects of the survey tool I created was the ability to have an open ended space where respondents could document what went well and what didn't on the project or effort. This might also be considered.

Another type of question might appropriately have a "Yes / No" response for those situations where the question or statement just doesn't fit into an "Agree / Disagree" response.

~~ This endth my current 02 cents. ~~

My email is at the bottom in case you'd like me to send you the *.jpg of my table relationships.

If you are absolutely sure the questions will never change then there is no reason to have the questions in a table.
You can create a an answer table that has tester_id, date, and a column for each of the answers and hard code the questions on 5 or 7 subforms or tabs.
Then each question would have its own button group associated with its own column.
Each form would have a "Next" button and the last from have an "End / Exit" button.

Let's take this back to basics for a moment. You have one table with a record for each tester. You have another table with a record for each question. You need to be able to record one answer for each tester, for each question.

That means that there are multiple answers for each tester (one per question); but there are also multiple answers for each question (one per tester).

In other words, you need to be able to record a Multiple to Multiple Relation.

The standard way to do this in a Relational Database is using a third table, as Greg and Michael Black described. The most important information in this third table is a pair of Foreign Keys: one relating the record to a Tester (using the Primary Key of the tester record), and one relating the record to a Question (using the Primary Key of the quesiton record). In addition, the records in this third table contain fields for whatever information you want to record for that particular combination of tester and question.

In terms of the user interface to gather the answers, I would suggest using two forms. The first form collects the information about the tester. The second form is a dynamically defined form to collect the answers. That means that, instead of building the form at design time, you write VBA code to create the form at run time.

As long as you are comfortable writing VBA code, this really isn't that hard. You create a recordset that reads the records from the question table, and a foreach loop that constructs a label (holding the question), an invisible text box (holding the ID of the question) and an option group (for the answer). Because you want to record each answer as a separate record in the Answers table, you don't bind any of the controls; instead, you have event code in an OK button that scans the controls and, for each set of controls, creates a record in the Answers table.

LOL, one of the databases I maintained had a 1-digit field for the year "because the life of the system would NEVER exceed the 9-year limit." It was a lot of work to add change the database to handle the tenth year.

I find it tough to believe that whatever worked today will continue to work tomorrow. <giggle>

Depending on the real objective of all this wonderfulness, the questions really have to be changed sometime imply because today's "bad practice" will hopefully be eliminated tomorrow and therefore no longer is a data point that needs be tracked. Today's issue will be eliminated by process or procedure changes and it becomes part of the way work is always done. Once internalized, hooray, and no longer an issue.

The original poster should go back to analysis and design phases of the project and build a more robust and dynamic application for the client. But as we all know there are folks that take the client's requirements as given for today and do not even think about tomorrow needs. These same folks hard code everything so that to change even a misspelling in a list the whole application has to be rebuilt and redistributed when if the values were pulled from a table only the data needs to be changed. I for one am the type of person that likes to forget about a application and leave as much of the maintenance and configuration as possible up to an administrator at the client's site. But then again, I am lazy.

Jeff & Michael, both of you have made valid points and I do try to foresee needs that aren't necessarily right up front and in sight; however, in this particular case, the test that is being administered is a standardized test that has been in paper form for many years. The goal is to reduce paperwork and the time it takes for the clinician to score and interpret results.

I have already anticipated the clinician giving the test more than once (getting a baseline, and then again later), the need to acquire statistical data for reporting (thus asking the tester for specific data upfront), and I anticipate that if the first computer testing model goes well, similar variations of the test will be added and will need to be identified back to the same tester_id.

I prefer not to re-do the program over-n-over, so I'm looking for the best solution upfront that I am capable of doing. I am comfortable with VBA although I am not an expert. I am not a programmer by trade.

I have never created a dynamically defined form, but I like Michael M.'s. thought about using one. I will have to check into it further though to see how to do it.

I'm still in the basic design part and definitely want to do it right the first time versus spend hundreds of hours on something that doesn't produce the desired result.

If you want to put your questionnaire over the top you can add a gauge to it. A simple gauge can be created in any graphics program, perhaps even PowerPoint. I use MS PhotoDraw but PhotoShop will work. For the needles, you can use thick lines or simple arrows. Save the images as .gif.

Your application would "move" the needles using the .Visible property to turn individual arrows on and off. The arrows that would be visible would be controlled by percentages calculated in the questionnaire. For the background of the gauge, I would suggest an image related to your industry or company.

I developed an Access application like this several years ago for a Future Farmers of America youth chapter in Texas. The idea was for the kids to see the progression of their knowledge as they completed a self-assessment. When their scores passed the acceptable range the background changed color. The hard part for me was learning how to use the Update and Append queries that I mentioned last night.

Depending on how your application works, you may need to have Requery or Repaint controls. This may require a little trial and error.

Rhonda,
I am working on a project that is similar to yours. However, I've been
working on the project for 3 years!
It will not change; there are specific requirements for the questionnaire;
and there are specific requirements for exporting the results.
Yeah, right... all of them have changed.
While I was creating the design... could we turn off questions as needed?
What about moving questions from one place to another?
I want to be able to have different question types, how hard is it to have
10 different types?
Ok... I digress... it's not easy.
At a minimum the tables you need are: respondent, question, responses, form
style, question type, sections
Respondent table has basic informaton about the "tester"
Question table has information about each question, FKs with question type
and Form style tables
Responses table captures the responses AFTER the respondent takes the
questionnaire
form style table: while it is nice to all questions the same style (e.g. 5
pt Likert as described in your question) it's not going to stay the same.
Sometimes a section will have a true/false question, a 5 pt Likert question
and then switch to scale of 1-100. So the form Style tells the system if
the the section has various question types or it's all the same.
Quesiton Type tables describes the type of question... 5 pt Likert,
true/false, yes/no, text, scale 1-10 or 1-100, etc.
Sections... this table splits the quesitonnaire into specific sections to
manage the questionnaire. There is a limit on the number of various
quesitons a section can handle... A form can have only 22 inches of data if
using multiple subforms.

When the respondent takes the questionnaire, the answers are recorded first
in the question table because each question is bound to the question type
subform or continuous form. Once the tester finishes and clicks "finished"
button the results are transposed and put into the responses table. Then
the question table results column is cleared ready for the next tester.

If you're going to do dynamic coding of each question there are other
things that need to be done. If on the 5 pt Likert questionnaire one
question is coded 1 thru 5 and the next question is coded 5 thru 1 then
there are a couple of more tables that are needed. If the coding is
straight forward then each checkbox can have the desired response code
assigned to it.

These are the kinds of issues that I too have addressed over the years and I agree that if they are not addressed upfront then a developer will be rebuilding their apps over and over. I mentioned some of them in my first post but that was just a start.

There is a sample questionnaire on the Net and a discussion of questionnaire designs in T. Hennig et. al. "Microsoft Access Small Business Solutions" from Wiley. There's also a discussion of gauges in Michael Alexander's "The Excel Analyst's Guide to Access" from Wiley.

I am glad that others have built Access questionnaires. They are not easy to design and develop and they become even more difficult when you have lots of users.

Wow, thank you all. I can see I have a lot of work ahead of me. I will be exploring some of the suggestions that you all have presented and let you know how it all turns out! There is definitely some areas that I had not thought of and I will need to sit down and put the pieces together to do the job properly (the first time) :) .