Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    31

    Unanswered: Questionnaire Table Link Suggestions Request.

    I am trying to make a Questionnaire Data base Using Microsoft Access 2010, my main objective is to make a database which is easy to update / add new questions and answers.

    I have come up with attacked Schema, which is working on individual form entry. However I would like to link Question Table, Client table, Answer table; as I think this may give better performance.
    I have Client Table with other personal detail fields. Question Table with all the questionnaire Questions and Answer Table with all the answers from questionnaire, I would prefer to have one single form for data Entry.

    Using attached Schema I can create individual form for data entry and feed data to respective table , however this not linking if I link all the table , I think is something to do with my primary keys.
    This is my assumption/normalisation: There may be some errors
    Each Client _Answer belongs to one Client 1:1
    Each Client_ Answer belongs to one Question 1: 1
    Each Client responds to many Questions 1: M
    Each Question is has one Answer 1:1
    Each Answer belong to one Question 1:1

    Please find attached Schema and see what can be done better,Much apprciated for your time and comments.
    Attached Thumbnails Attached Thumbnails ERD.png  
    Last edited by john100; 05-09-13 at 12:56.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    - Each Client _Answer belongs to one Client, however I suppose that one Client can answer several questions (i.e. provide several answers).
    - Each Question is has one Answer, right but only for one client (i.e. different clients will provide different answers to the same question).
    Have a nice day!

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Have you done any research on relational databases in general and Access in particular? I ask because you've set up some really freaky links:
    • You've defined a 1:1 link between questions and answers
    • Each client can only be asked one question
    • The link between ClientAnswerT and ClientT makes no sense at all

    Consider instead:
    • A table of questions (ID, number, question, type [free answer, single options, multiple options, etc])
    • A table of clients (ID, name, other details)
    • A table of interviews (ID, date, client ID, question ID, answer)

    If you use questions with fixed answer from which the clients choose one or more answers, you can create a table for these as well. This table should include an ID, the question ID, option value and a numeric value. For questions that can have multiple answers (e.g. "Tick your three favourite days of the week"), the numeric values should be powers of 2 (0 for the first, 1 for the second, 2 for the third, 4 for the fourth etc). This way you can store the sum of these values for the selected options in the interview table. When you come to report on the answers, you will be able to back-calculate the selected options from the single number.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Mar 2013
    Posts
    31
    Thanks so much I will try that.

  5. #5
    Join Date
    Mar 2013
    Posts
    31

    Spell Checker on Access DataBase (Form)

    I am trying to validate/ correct spelling errors on Data entry form any one know how to work it out. I am using Microsoft Access Database.
    Last edited by john100; 05-09-13 at 15:26. Reason: received answer for first question

  6. #6
    Join Date
    Mar 2013
    Posts
    31
    Thanks for your reply the link were just a quick scramble as I needed some opinions from the forum. ClientAnswer table come as result of many to many relationship hence needing a linker table Thanks so much for your help much appreciated this set me on right direction.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •