For John Vinson

[dee]

Hi John,

You have so kindly responded and been very patient in the past, and I am
hoping that I can ask your advice again.

I am modifying AYS and things are coming along pretty well. My only problem
is that while I can limit the answers to the list or allow free input, I
can't select multiple answers.

I am wondering if you had encountered this type of problem in the
questionnaire database you mentioned. I will be entering the questionnaire
data from pre-printed forms that have been filled in. They would like to to
show the question text, then the answers and be able to select the one
circled on the hard copy.

I'm not sure how to do this in that, as I mentioned several questions (but
not all) have multiple answers that can be selected.

Duane had suggested that I basically convert each answer into a question.
So, instead of What language do you speak? with answers of 1 - English 2 -
French 3 - Other, I have three questions - 1 - Do you speak English? 2 - Do
you speak French? 3 - What other language do you speak?

Unfortunately, the design of the questionnaires really wasn't with thoughts
of the database and they really want to be able to print/view the questions
and answers as they are on the forms.

It would be great to have the question, then the answers with a check box
for Y/N and then a text box for comments, but the only way I can seem to
accomplish this is to have a separate field for each and every question,
which seems totally crazy.

If you have any advice on how I can handle this based on your experience, I
would really appreciate it.

Thanks so much, John.

 

[John W. Vinson]

I am modifying AYS and things are coming along pretty well. My only problem
is that while I can limit the answers to the list or allow free input, I
can't select multiple answers.

I am wondering if you had encountered this type of problem in the
questionnaire database you mentioned.

I don't believe so. As I think I mentioned, this was a work-for-hire and I no
longer have a copy of the result on my database, and I don't remember the
details. You may need to extend the structure of AYS by adding another text
field... I see you're discussing this with Duane so unless he doesn't want to
continue the discussion I'll leave it at that.

John W. Vinson [MVP]

 

[John W. Vinson]

So, I'm at a bit of a dead end. When you mentioned an extra text field,
would this be for the question text?

No, it would be in the answers table and it would be where the user enters
their non-pre-scripted reply.

I don't happen to have AYS downloaded on this computer (and my old computer's
flaky enough I'm reluctant to boot it up!!!) - and I'm sure you've adapted
Duane's tables. Could you post the relevant table definitions for your current
structure? e.g.

Tablename
Fieldname <datatype> <primary key>
Fieldname <datatype>
...


John W. Vinson [MVP]

 

[dee]

Absolutely!

TBLPARTICIPANTS
pptNo (text) PK
pptLast (text)
pptFirst (text)
pptPhone(text)
etc.

TBLSURVEYS
surveyId (autonumber)(PK)
surveyName (text)
surveyDescription (text)

TBLHEADINGS
headingID (autonumber) (PK)
surveyID (number) (FK from TBLSURVEYS
mainHeadingNo (text)
mainHeadingText (text)
subHeadingText (text)
(This table is to normalize TBLQUESTIONS, as many questions are related to
one heading or sub-heading, and some survey have no headings)

TBLQUESTIONS
questionID (autonumber) (PK)
surveyHeadingsID (number) (FK from TBLHEADINGS)
questionNumber
questionText (text)
answerType (numeric)
limitToList (Y/N)
answerValidation (text)
questionMask (text)

TBLANSWERS
answerID (autonumber) (PK)
questionID (number) (FK from TBLQUESTIONS)
answerText

TBLRESPONSES
pptNumber (text) (FK from TBLPARTICIPANTS) (combines with questionID as PK)
questionID (number) (FK from TBLPARTICIPANTS)
response (text)
surveyorID (text) (FK from TBL SURVEYORS)

TBLSURVEYORS
surveyorID (text) (PK)
surveyorFirst (text)
surveyorLast (text)

I think that's it! Thank you!


--
Thanks!

Dee

 

[John W. Vinson]

Absolutely!

Working on income taxes today.... I'll try to get to this sometime soon.

John W. Vinson [MVP]

 

[dee]

Thank you so much. I look forward very much to your suggestions. I think
I'm going to go bald soon because I'm pulling out so much hair!

Good luck with taxes - never a fun time!

--
Thanks!

Dee

 

[dee]

Hope your taxes are done and not too painful!

I have worked the last many days on this database. I have one part figured
out nicely, but the darned variety of answer types is still beyond me.

One thing I came across seems to be the notion of writing VBA code that
looks to see what type of control the question requires (I could add a field
to my tblQuestions that has a 1 for y/n, 2 for combo, 3 for list box, 4 for
text box). I haven't been successful in getting it to work, mind you. Ths
is the code:

----------

What you need is to include info in the question record that identifies the
format the question form. For example, You might have a control field that
identifies whether its a combo, text box, check box etc. You might have
another field only for combos that includes a rowsource. So, in the On
Current event of the question form you have code like:

Select Case Me.ControlType
Case "Combo"
Me.cboAnswer.Visible = True
Me.cboAnswer.RowSource = Me.RowSource
Me.cboLabel.Caption = Me.Caption

Case "Check"
Me.chkAnswer.Visible = True
Me.chkLabel.Caption = Me.Caption

etc.

End Select

RowSource, Captioon and ControlType are hiddn controls on your form that
hold the values needed to format the form. So, if the ControlType is Combo,
then the combo is made Visible, the RowSource is set to the list of languages
and
the label is dynamically set.

-------------
I've also thought of having some kind of sub-table that contains only the
multiple selection answers, but this probably doesn't make sense.

I know I could also have one field for each and every question, but with
about 6 surveys with up to 50 or 60 questions and about 3000 people filling
them in, it may not be the best idea.

Thanks for any help - I am really trying hard to figure this out, but am at
a loss and just don't know where to turn next.


--
Thanks!

Dee