Re: ?bound fields

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Andrew Smith (andydsmith_at_ntlworld.invalid)
Date: 02/19/04 

Date: Thu, 19 Feb 2004 22:53:50 -0000

No, I don't think a separate table for each questionnaire would be a good
idea. Also, lookup fields in tables are considered to be an abomination and
to be avoided at all costs:

http://www.mvps.org/access/lookupfields.htm

I suspect you are used to working with spreadsheets, and are thinking of a
database as being like a spreadsheet, which it isn't. From what you have
said I think you need five related tables:

Questionnaires:
    QuestionnaireID (Primary key)
    QuestionnaireDescription
    QuestionnaireDate
    Other fields about the questionnaire

Questions:
    QuestionID (Primary key)
    QuestionnaireID (Foreign key, linked to the Questionnaires table)
    QuestionNo
    QuestionText

QuestionOptions
    QuestionOptionID (Primary key)
    QuestionID (Foreign key, linked to the Questions table)
    QuestionOptionNo (ie a, b or c)
    Prompt (ie the text presented to the user)
    Answer (ie the text you send back if this response is selected)

Users:
    UserID (Primary key)
    FirstName
    LastName
    Other fields about your users that you may need (eg contact details)

Responses:
    ResponseID (Primary Key)
    UserID (Foreign key linked to the Users table)
    QuestionOptionID (Foreign key linked to the QuestionOptions table)
    This table stores the answers given by each user to each question.

So, each questionnaire can have any number of questions in the Questions
table. Each question can have any number of options in the QuestionOptions
table (you only need 3, but this is not really important to the table
design). For each option you store the number (a, b or c), the prompt that
is shown to the user and the text that you will return.

You will need to store some information about each of the users (people
filling in the questionnaires), and their answers to each question. These
are stored in the Users and Responses table. The users table should be
straightforward. I'm not quite sure how I'd design the Responses table -
you'll want to ensure that there can only be one response to each question
and the design shown above gives you no way of enforcing this, so you'll
probably want to include QuestionId in this table too (then you can create a
unique index on the UserID and QuestionID fields and this will guarantee
only one response per question).

(Sorry for the delay in posting this message - my ISP's news service has
gone down, and I'll not be logging on again for about 24 hours!)

"Marion" <anonymous@discussions.microsoft.com> wrote in message
news:1318501c3f6f2$c34826b0$a601280a@phx.gbl...
> Thank you for your advice - do you think it will work if
> I create a separate table for each questionnaire, within
> that table, a separate field for each question with each
> field being a look up field where I can select the
> response depending of whether the customer has selected a
> b or c.
>
> (on the questionaire that customer has to select 1a or 1b
> or 1c and I have a set script which I have to send back
> depending of which one he has selected)
>
> Many thanks Marion
>
> >-----Original Message-----
> >Marion,
> >
> >I think that your problem is essentially the result of a
> poorly designed
> >table. You actually have a one to many relationship of
> questionnaires to
> >questions, but have put everything in the same table -
> hence the fields
> >"question1", "question2" etc. If possible, I would
> suggest that you change
> >the table structure - this will not be the only case
> where this table
> >structure will cause you problems in the future.
> >
> >If for some reason you can't or won't redesign the
> tables then you will have
> >to use VBA code to change the control source of the
> answer field. The code
> >would go in the AfterUpdate event of the options combo
> box. I don't
> >understand what you mean by "Each field has 3 records to
> reflect a choice of
> >answers a,b or c and a corresponding answer to suit",
> so I can't help you
> >with the code.
> >
> >"Marion" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:1185801c3f5f0$c9b5d900$a101280a@phx.gbl...
> >> I can not get my form to display the correct
> >> information. My table consists of fields such as
> >> question1 answer1 question2 answer2 etc. Each field
> has
> >> 3 records to reflect a choice of answers a,b or c and a
> >> corresponding answer to suit
> >>
> >> I have set up my form to have drop down box to select
> one
> >> of three options for question1 but how can I get my
> >> answer1 box to automatically select the corresponding
> >> answer to suit the drop down option selected in
> >> question1. At present all three answers show. I have
> >> set the answer1 field up as a memo field as the text in
> >> the answer is quite long.
> >>
> >> Any help gratefully received
> >>
> >> Regards
> >>
> >> Marion
> >
> >
> >.
> >

Relevant Pages

  • Re: Form Design
    ... I don't understand why you need a separate table (nor a separate ... "Trish at St Andrews" wrote ... >I currently have a table containing data where the primary key indicates ... > the design related to this new table to be the same as that for last year. ...
    (microsoft.public.access.gettingstarted)
  • Re: Likert Scale data entry
    ... I also have the "survey results" table, ... I can record the numerical value of each response in a separate field ... The primary key is survey_nbr & question_nbr. ... The ComboBox RowSource would look like this: ...
    (comp.databases.ms-access)
  • Re: BeforeUpdate event issue
    ... The primary key cannot be a duplicate. ... Private Sub Form_BeforeUpdate ... Response As Integer) ... MsgBox "You have entered a File Code that already exists in the ...
    (microsoft.public.access.forms)
  • Re: multiple column primary key problem
    ... Private Sub DataGrid1_Error(ByVal DataError As Integer, Response As Integer) ... how i can use this event to check the unique of primary key? ...
    (microsoft.public.vb.database)
  • Re: Design help for capturing survey results
    ... The basic structure would be these 4 tables: ... QuestionID Number primary key ... AnswerNum number of this answer ... Response table (one record for each answer given. ...
    (microsoft.public.access.tablesdbdesign)