Advice creating survey form 1

[Jeffr0]

Hello,

First let me thank the folks on this forum. It's been an invaluable resource for me learning Microsoft Access, though until now I've been strictly lurking.

I would like to represent a 3-page survey as a form (probably using a Tab Control) in Access 2007. The form should look as close to the paper survey as possible; its audience is a data-entry team that will enter results from a mailed paper survey. I've borrowed some ideas from an older http://www.tek-tips.com/viewthread.cfm?qid=1447289]thread on survey table organization[/url] to put together this schema.

First, the survey, respondents, and junction table for many-to-many mapping:

tblSurvey:
  SurveyID              (pk)
  SurveyName

tblRespondent:
  RespondentID          (pk)
  FirstName
  LastName

tblSurveyedRespondents  (junction table)
  RespondentID          (fk to tblRespondent)
  SurveyID              (fk to tblSurvey)

Next, how the questions on a particular survey are represented:

tblQuestion
  QuestionID            (pk)
  QuestionText
  SurveyID              (fk to tblSurvey)
  AnswerID              (fk to tblAnswer)

tblAnswer
  AnswerID              (pk)
  AnswerText

Some advice, if you please, on what techniques to use to create a form that represents a page on the survey. For example:

Form Header
  - Shows current survey (tblSurvey.SurveyName)
  - Shows current respondent (tblRespondent.LastName)

Form Detail
  - Text for Question 1:  [Answer for Question one goes here]
  - Text for Question 2:  [Answer for Question two goes here]
  <etc>

Form Footer
  - Not used

For example, "Text for Question 1" could be a static textbox whose content is tblQuestion.QuestionText for QuestionID 47, say. And its corresponding [Answer for Question One] could be a textbox bound to create a new record in tblAnswer. Perhaps then, some VB to populate the newly-created Answer's ID into tblQuestion.AnswerID for QuestionID 47, once an answer for this question is entered and validated.

Then "Text for Question 2" could be a static textbox whose content is tblQuestion.QuestionText for QuestionID 158, for example; and [Answer for Question Two] could similarly be a textbox bound to create another new record in tblAnswer, whose newly-created AnswerID gets passed into tblQuestion.AnswerID, this time for QuestionID 158.

And so forth. But that's just my guesswork on how to approach this form. It might be all wrong.

I am at a loss on how to approach this form design, paralyzed by possibilities and lack of Access hindsight! Suggestions? Some other details:

* It is ok to hard-code an association between QuestionIDs (47, 158, etc) into the form controls if necessary. Remember that the forms are already printed; they are a done deal.

* Probably 20-30 questions per Tab Control.

* I have simplified in my example above. There are actually checkboxes to represent answers, the schema is a little more complex, and so forth.

* A Datasheet view into Questions won't work because one requirement is for the form to look like the actual survey, with checkboxes, graphics, lines, formatting, etc.

Thanks very much!
-Jeff

 

[MajP]

Have you looked at

http://www.rogersaccesslibrary.com/forum/topic3_page1.html

This is highly recommended on this site.

 

[dhookom]

You could probably use something like At Your Survey as MajP points out. The subform in AYS is continuous. It sounds like you might need to change this to single form view where you can change the format of the controls on the subform.

Duane
Hook'D on Access
MS Access MVP

 

[Jeffr0]

Thanks for the prompt responses!

Yes, I have looked extensively at AYS before asking my question! It was instrumental in helping me refine my schema. Good stuff. But I can't find the answer I need for designing my survey form.

I need the form to look exactly like the paper survey (well, very similar at least) -- so the continuous form in AYS won't work.

I'll futz around a little more with the form and see if I can't figure it out. I just basically need to populate a Text Field with the value of a specific tblQuestion.QuestionText, carefully place that text field on my form, and give it a corresponding Text Field for the data entry operator to enter the answer from the paper form.

Probably what I'll try is using a simple expression to select a particular question for the question Text Field by QuestionID; and I'll try some VB on the Text Field representing the answer, to see if I can figure out how to link that new answer ID to the specific question's tblQuestion.AnswerID.

Thanks,
-Jeff

 

[MajP]

Duane has more experience in this area, but looking at your tables I would think you are missing a table.

I would think that tblQuestions has unique information about questions and a fk to the survey that it is on. But I can not see how it would have a FK to answers.

I am assuming anwer table contains the type of answer that you can choose. "Yes", "No", "Strongly Agree", "Disagree"....
If all of your answers are free text then I would not think you need an answer table.

Normally I would have a junction table

juncTtblRespondent_Question_Answer
respondentID_fk
questionID_fk
answerID_fk

I normally do this with unbound forms and code, but if I was using bound forms I would do the following.
As soon as I associate a respondent to a survey I would run an insert query that puts a question ID for each question and the respondentID into the above junction table. Then the form would show all questions for that respondent and I would just select the answers.

If all questions are free text answers, then replace the answerID_fk with answerText.



normally my tblAnswers is the possible choices, and the above junction table are the selected answer. My answer table usually looks like

answerID
answerType
answerDescription

Where type is the type of choices

LikertAgree: Strongly Agree, Agree, Neutral, Disagree, Stronly Disagree
BooleanTrueFalse: True, False
BooleanYesNo :Yes, No
LikertPrefer: Prefer, Neutral, Do Not Prefer
etc.

So in my question table, I have an answer type. So Then I can present the correct type of answer for a given question. A true false question sees the true/false choices; a likert scale question sees the correct likert choices.

But let Duane chime in because he has more experience in this area.

 

[Jeffr0]

Hi MajP, thanks for your input. Let me address a few things here.

"Text for Question 1" could be a static textbox whose content is tblQuestion.QuestionText for QuestionID 47, say

I answered my own question for this one. To populate a Text Field with the contents of a specific field in a specific record, I set the Text Field's Control Source to the following expression:

=DLookUp("[QuestionText]","tblQuestion","[QuestionID] = 47")

I am assuming anwer table contains the type of answer that you can choose. "Yes", "No", "Strongly Agree", "Disagree"....
If all of your answers are free text then I would not think you need an answer table.

The answers are all free text.

As an aside, I have created a tblQuestionType lookup table with "Demographic", "Yes / No", "Integer", "Percent", "Text", and "Currency" values to describe how the free text answer to any particular question could be interpreted, if necessary. I'm not sure I'll even end up using this Question Type concept, but I wanted to put it in place anyway, because as a C++ application developer, I do like typed data, and this is a pretty close approximation. It just felt right.

Normally I would have a junction table

juncTtblRespondent_Question_Answer
respondentID_fk
questionID_fk
answerID_fk

[bold]<...>[/bold]

If all questions are free text answers, then replace the answerID_fk with answerText.

Aah, it's been a while since I did DB design, but I see what you are saying. I will revise my tables as such:

tblQuestion:
  QuestionID -- pk
  QuestionText -- Text of the question ("How often does...")
  QuestionTypeID -- fk to tblQuestionType
  SurveyID -- fk to the Survey ID on which this question appears.  

tblQuestionType
  QuestionTypeID -- pk
  QuestionType -- "Yes / No", "Demographic", etc

tblJuncRespondentQuestionAnswer
  RespondentID -- pk, fk to tblRespondent: The ID of the respondent answering the question
  QuestionID -- pk, fk to tblQuestion: The ID of the question being answered
  AnswerText: The respondent's free text answer to the question

There is no need for a tblAnswer with this design, as I understand you are saying.

(oh BTW, for my system, a single Respondent can take multiple Surveys, and a single Survey can be taken by multiple Respondents; therefore, there is a many-to-many mapping between tblSurvey and tblRespondent that is maintained in a tblJuncSurveyedRespondents that I'm not showing for clarity).

I normally do [juncTtblRespondent_Question_Answer] with unbound forms and code, but if I was using bound forms I would do the following.
As soon as I associate a respondent to a survey I would run an insert query that puts a question ID for each question and the respondentID into the above junction table. Then the form would show all questions for that respondent and I would just select the answers.

Brilliant. That's exactly the kind of hindsight I was looking to leverage, thank you. I think I sett a light at the end of the tunnel. So I can set up my form's data source to juncTblRespondentQuestionAnswer. After the user comes into the form and selects a Respondent (or enters a new one in a modal dialog), I then perform the aforementioned insertion query with VB. The form's question Text Fields are populated with QuestionText from the various hard-coded QuestionIDs (via DLookup) to match the paper survey.

How would I bind the form's various answers' Text Fields to the correct juncTblRespondentQuestionAnswer.RespondentID and QuestionID values? I guess I would have to hard-code the QuestionID, and I could query the RespondentID from the Form, so it sounds like a job for a VB AfterUpdate event handler for each Text Field? With this design, it seems like there's no point binding the form to any data source at all; perhaps that's what you meant with "unbound forms and code".

Thanks again,
-Jeff

 

[MajP]

My original suggestion to run an append query was really to support a bound form. That way you already have placeholder records and can display them in a continous form or single form view. You need to make the decision which solution will work best for you. When you start coding an unbound form, you will quickly realize the power of Access bound forms. A bound form does so many things, that you will have to code. You will easily be able to sort questions, add questions, delete questions etc. However, with that said there are times where an unbound form provides flexibility that can not be done with a bound form.

Bound solution:
So if it was bound, after I run the append query into juncTblRespondentQuestionAnswer I can build a form with a subform.
1. The main form would be based on a query joining respondent table to tblJuncSurveyedRespondent. So that I have information about the respondentID and the surveyID.
2. The subform would be based on a query that joins tblQuestions to tblSurvey to juncTblRespondentQuestionAnswer.
3. The main form would link to the subform by SurveyID and Respondent ID

Now when you change the respondent ID and the survey ID in the mainform you would see automatically all the questions for that survey for that respondent. Just fill in the answer. The questions can be shown in a continous view or single form view.

Form me the main form would not be bound. I would only have 2 comboboxes on the main form: cmboPerson and cmboSurvey. These would be cascading combos. If I select a persons name, the second combo would only give me the surveys that the person was assigned. Then I pick a survey. The subform is linked to these two combos. So automatically once I pick a person and a survey the subforms automatically populate. I would not try to assign people to surveys on this form. If I need to do that I would have a popup form to add assign a person to a new survey. The assignment is what fires the code to add questions into the junction table.

If you can go bound, I would strong recommend it. That is a lot going on with very little code required. If you go unbound, all of that needs to get coded and each form will be a lot more tedious to build. There is probably a lot of formatting that can make the form look a lot like the paper form and still be done bound. Or at least present the questions in an order that flows similarly.

If you still need to go unbound, then come back. If you can provide an image of the paper survey that may help to discuss how closely you could get with a bound form, and if an unbound is truly needed.

You also want to add a sortOrder field to your tblquestions. That way you can add or delete questions and present them in some logical order. You may want questionID 68 to appear as the first question, etc.

 

[Jeffr0]

Thanks for your suggestions regarding the form binding. I don't completely understand the process, but I will spend some time working through it tonight with a continuous form just to keep things simple, and see if it makes sense.

I've attached a sample survey page. The top section is the respondent data (name, title, etc. Ignore the stuff about plans and honoraria). Then, you will see four sections of questions.

I would be curious to know if this is something that I might be able to represent using a bound single form view. One thing that is unclear to me is how to add a Text Box to a bound form, and tell Acces "this text box corresponds to Question ID 482. When the user types something in, store that in 'AnswerText' for QuestionID=482. Is that something I can set 100% through properties? Or will I have to put some VB in for each Text Field?

And, a note about the checkbox grids -- these are tricky. What I've done to represent these in a simply fashion is broken the two checkbox grids on the last section into multiple questions. For example, I envision the data looking like this in the DB for the bottom-left grid:

Question Free Text | Answer Free Text
--------------------------------------
Armidex 2010: Medical Benefit
Armidex 2011: Both
Casodex 2010: Pharmacy Benefit
Casodex 2011: Pharmacy Benefit


The bottom-right grid would be similar:
Question Free Text | Answer Free Text
--------------------------------------
Armidex Prior Authorization: Yes / No
Armidex Peer Reviewed: Yes / No
Armidex Compendia: Yes / No
<etc>

This is a different discussion topic entirely, so I won't go into the rationales for this, to preserve our focus on the form design.

Thanks again.
-Jeff

 

[Jeffr0]

Update --

After speaking with the project manager, I think we can build the form like this to simplify things:

frmSurvey (Main Form)
- cmbRespondent for selecting a Respondent
- cmbSurvey for selecting a Respondent's Survey
- cmbRespondent and cmbSurvey are cascading, implemented via VB. Inserts placeholder (blank) Answers if a new Survey is associated to a Respondent.

fsubQuestionsAnswers (subform)
- Continuous form
- Parent form is frmSurvey
- Displays all the questions and answers for the selected Respondent's selected Survey

Hopefully that simplifies things a bit. It also makes the design a lot more scalable since it doesn't require development work each time a new survey comes out; rather, an admin can just enter in a new Survey, add questions to it, and they are done.

I've been poking around tonight and can't seem to figure out the best way to set up the continuous form, especially since the cmbSurvey and cmbRespondent are cascading and therefore have their RowSource properties set in VBA to a SQL query rather than during design time.

Taking a step back...

* Do I need a form/subform relationship here? Or can I just put the cascading Respondent & Survey combo boxes in the HEADER, and make the DETAIL continuous?

* Assuming form/subform relationship, I'm not sure exactly how to set fsubQuestionsAnswers's data source to be "the questions and answers for the RespondentID and SurveyID selected in the comboboxes". Is this something I can do at design-time via properties, or do I have to do some VB? Access is saying "Can't build a link between unbound forms" when I try to change the Link Master/Child Fields property.

Almost there! Thanks as always.
-Jeff

 

[MajP]

I would put the two combos in the form header. They are unbound and used for navigation only. If you need to add a person (not in the list), I would pop open a form for entering people. It will make it a lot easier to seperate the data entry of persons information from this form designed for entering survey data.

The top part of your form is demographic information. I assume all of those fields are in your tblRespondent. This then can go on a subform. I would personally make this read-only. To edit/add a person I would pop open a dialog form to enter or change respondent information. Same form as mentioned above for adding a person. This subform is linked to cmboRespondent. Change the combo and this subform goes to the correct respondent.

Now each question will also need a field to identify the question category; "questionCat". These would be things like: Health Plan Spend, Physician Reimbursement, Preferred agents. That way you can filter your questions onto individual subform and add group headers.

Now to make the rest of this work each question has to be presented in a single line if you want to use a continous form. So that you can present two fields, questionText and answerText. The questionText field is locked and the answer text field is not.

So for example you would have to change your first question into two questions
"In 2010 approximately what percentage..."
"In 2011 approximately what percentage..."

Box II 1.a would have to be 4 seperate questions
In 2010 what is the reimbursement rate for ASP?
..... for AWP?
In 2010 is the reimbursement rate capped to speciality network?

In part IV the questions can be written in a way that the choice for armidex is a single choice: from a pull down of Pharmacy Benefit, Medical Benefit, or Both

Part V.1 would end up being 40 seperate yes not combinations.
each medication by each colum asked as a yes/no question.

However, I see very few questions that I consider free text. Free text questions are questions where the respondent can write anything. Such as "Please describe how we can improve our service". Most of your questions have discrete choices.

This gets hard to do in a continous form to present different type of choices for each question.

So know each group goes into its own subform. So on this form you would have a subform for Part 1,2,3,4,V,V.1. Each of these subforms is based on a seperate query that joins tblSurvey to tblQuestion to tblRespondentQuestionAnswer. Each subform query is filtered by category. Each subform links to both combos by respondentID and Survey id.

Each answer in each subform can come from combobox or have a text box. As long as within each subform the choices in the combobox are the same. So in Part1 the subform would have a textbox to enter a percentage. Part V subform would have a combobox with the choices of Pharmacy, Medical, Both. Part V.1 would have a yes no combo (or a checkbox). So in each subform it is continous with two fields
questionText followed by the answerText (or answerID)

This would work well except for where you have 2 different types of questions in a single Part. In part II you ask for a percentage (free text) and then the others are yes/no choices.

 

[MajP]

Can you discuss your use of these surveys? How often do you make them, how many different ones, how are they administered, how many are processed, how is the data entered etc.

The reason I ask would really determine what level of flexibility is needed and the design.

From the one form you showed, it would be very easy for me to make that an unbound form. I then could keep the exact format and write simple code to update the table.
However every time I change a question it would require redesign of the form and change in code.

If I built a bound continous form it will take more time, limit the presentation, and be a more complicated. But if I do it right then I can add and modify future questions and surveys without having to change the form or code.

So if this is a one time shot versus on going approach, my strategy would be different.

 

[Jeffr0]

Sure, here are some details about the survey. This database needs to contain many different surveys, about a dozen a year; each survey is similar in style, but unique in content (the questions asked; the choices to select). They are mailed to respondents, who complete the forms, and then mail / fax back, and they are then entered into Access by someone who is familiar with the task. As such, the questions on any particular survey DO NOT CHANGE once they have been mailed out.

I like the idea of grouping questions, and then using that grouping to define subforms.

I went with a "free text for all answers" approach. I didn't really know how I could assign different question types (Percentage, Free Text, Yes / No, Integer, Currency) to each question's answer, and wasn't familiar enough with Access to know whether it would support this complexity on its forms (if question-type is Yes / No, then show a checkbox; if question-type is Free Text, then show a text field" etc). So everything became free text.

I will work a little more this evening. I need to have something in place by Monday so I'm probably going to have to go with the easiest UI approach for now, and shelf design strategies until a later date. I am curious how you say "it would be very easy ... to make that an unbound form". It is true that a question doesn't change. Can you elaborate? My thoughts all along were to use an unbounded form, and simply go to the trouble of writing a little VB for each question to validate the answer and manually store it in AnswerText. I think it boils down to my being much more proficient in VB than in Access, so I gravitate to unbounded forms and code versus mysterious (to me) Access form/subform implicit relationships and magic. Ask me again in two years and I might have a different answer!

Thanks,
-Jeff

 

[MajP]

What I was saying is theoretically you could build the database application using bound forms that is flexible enough that a user could enter/modify/delete some new questions and spit out a brand new survey without touching any code or form/report design. You could add hundreds of questions without any problem. But that takes a lot of forethought to get to that point. You will have to design forms and reports of the application to meet the capabilities and limitations of Access.

If you are going to be around to design the forms as new ones come up, and they look like what you showed, and it is only 12 a year you may find it easier in this case to make it unbound. The other beauty is that you could make a Word form and put the code in there that automatically updates your database. Send the users a Word form and they send it back. Now the amount of code for unbound will be far greater but mainly repetititve. It then would make it easier to have lots of formatting and different types of controls and control choices for input. If I was going with an ubound solution I would not even pull, the questions from the database. I would just type them in as labels. Obviously this takes out even more flexibility, but if you go unbound you have to code each control seperately anyways.

It is a tough call reuseability versus flexibility.

 

[Jeffr0]

Just a follow-up. I ended up implementing the form as unbounded, and simply doing a bunch of VB coding. I discovered control choices last night, which allowed me to group some of the checkbox clusters and it makes thing a lot less painless. You are right, it is still a good bunch of code, mostly cut-and-paste (shudder!), and gives me some appreciation for the automation that bounded forms provide.

I have a separate question (regarding best practices for manually serializing a form to DB, and from DB), but I'll start it in a new thread since it's fairly general and not specific to this thread.

Thanks again.
-Jeff