Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Question Unanswered: newbie design question (access) - please help.

    Hello - looking for help on a school project.

    My 'customer' (a friend) is an MD who gathers survey data from patients & uses the data for research. The information is fairly qualitiative ("my health has been...1)excellent 2) not bad 3) really awful." )

    The surveys are standard (I can't change them) and are administered on paper. The goal is to make it so that her secretary (a beginner MS Access user) can do the data entry. Then she needs to push a button and have the db generate some simple reports that recombine the questions and their answers on a patient by patient basis.

    So far, so good. But - the hang-up is that each survey has lots of multiple choice questions, and the choices vary in number (some have 3 choices, some have 2, 5, 6, etc) and the implication of a particular answer varies from question to question (some are 1=Yes, 2=No, some are 1=Excellent, 2=Good, etc).

    I want to minimize the the amount of typing needed to do the data entry. I assumed this meant that I should have lookup tables providing choices to be listed on the form, but now I'm not so sure - I can't figure out how to have tables that have the questions and the proper set of answers for each question without having a separate table for each question (or for each set of questions with a common set of answer choices). Plus I just finished reading the "Lookup tables are evil" link..

    I can't find an example that correlates my problem closely enough for me to grasp the essence of a good solution.

    NOTE: my coding skills are essentially nil. I know the fundamentals of relational databases in general, but I'm learning Access as I go along.

    If anyone has A) something similar I can look at and teach myself or B) the patience to make some recommendations, I would be eternally grateful.

    thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    How many questions are in the survey? Can you post or E-Mail a copy of the survey?


  3. #3
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I would also suggest using Option Groups. They allow you to use Radio buttons, Check boxes, or Buttons with each having is own numeric value, but also allowing you to select the how many you need for each one.

    (You could basically re create the Survey on the form.) That is what I would do. Make it as much like the real survey as possible. Makes for less mistakes.

    I just finished updatings someone's Graduation Application Database. I made it look as much like the original application as is possible.

    Hope I can help further if you have any more questions,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  4. #4
    Join Date
    Apr 2004
    Posts
    66
    Since I'm pretty new to all this, too, I thought it might help to hear how someone would set this up, step by step...

    JS is right that radio buttons would work here - I've used them and they are really much easier than it sounds! (there is a wizard that does it for you!)

    You would then set up a table with patient ID# and a field for each question answer (as a number)

    Then set up a table for each question that lists a number for each multiple choice answer. For example,

    Table: MyHealthIs
    Number: Description
    1 Excellent
    2 Mediocre
    3 Crummy

    So, if you set up a form that matches the survey, the secretary uses the mouse to click the answers, and those fill in the spots on the main table:
    Table: Main
    Patient ID Q1 Q2 Q3 Q4 etc.

    Use relationships to link the tables and you should be all set.

    Hope that helps.

    Susan

  5. #5
    Join Date
    Apr 2004
    Posts
    6
    thanks to all of you for the input. It's looking like I will turn in a build of this on Friday for a grade, but continue helping the friend as she expands her research.

    cyberlynx - no soft copies right at hand, but I will likely get them soon & post for feedback from all and sundry. Hectic week prevents swifter response.

    JSthepatriot: i'll read up on option groups (not sure how these differ from giving the LookupWizard a hand-keyed list of the values I wish to use.)

    Mommyof4 - I was really hoping to avoid making tables for every question. I'm still learning the fundamentals of good design, but it seemed that I'd be swimming in tables if I went that route. Wouldn't that bode ill for adding new surveys to the mix later on?

    - m

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    A lookupwizard uses a temporary table to look up the items in another table. OptionGroup allows you to group the answers to a question and if it is Yes out put 1 if it is no out put a 2 and so on. You can take those values and when you click the check button at the bottom it make a report of what went wrong at the same time inserting it into a database.

    I would have to agree that creating tables for every question would be quite cumbersome.

    Okay you will want to consider the following three tables.

    One Patient table with all patient information needed.

    One Questions table to hold all questions.

    One Answers table to hold all patients answers.

    The Reason you need the three is to keep data duplication to a minimum.

    This college page on 'Normalization' I think would be a good thing for you to read so you can decide what tables you need.

    http://www2.volstate.edu/hmb/cis161/normalization.htm

    Hope all of this helps in some way,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Apr 2004
    Posts
    66
    JS - How would you set up the table on the questions then - I just can't picture this, since some have three answers, others may have 5... I deal with similar issues and have always set up separate tables...

    Susan

  8. #8
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    The questions table would only hold the questions not the available answers for that question (since the answers are right or wrong, no validation needed).

    The Patient table setup I think is obvious...

    PatientID (AutoNumber) or Unique Number---Primary Key
    FName
    MName
    LName
    Address
    City
    etc.

    The Questions Table should be...

    QuestID (AutoNumber)---Primary Key
    Question

    The Answers Table...
    AnsID (AutoNumber)---Primary Key
    QuestID---Foreign Key
    Answer

    That way you can have many answers for one question using a one-to-many relationship.

    You would then probably want another table to store the answers by the patientID.

    Let me know if any of the above makes sense. I hope it does.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  9. #9
    Join Date
    Apr 2004
    Posts
    66
    OOOooooohhhhhh......

    Hmmmmmm....makes me think about it in a whole new way!!

    Thanks a ton!

    Susan

  10. #10
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Not a problem at all .

    Glad to have been of service.
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  11. #11
    Join Date
    Apr 2004
    Posts
    6

    i'm with you so far....

    so tables are

    patient (PK ptID)
    patient answer (PK ???, FK ptID, FK answerID. PK = FK + FK??)
    answer (PK answerID, FK is quesID)
    question (PK quesID)


    I see the logic of splitting answer vs. patient answers, but I'm still wrapping my head around the answer table (working on that, esp the key ??) and capturing the answers properly.

  12. #12
    Join Date
    Apr 2004
    Posts
    6
    i think that my current design (which has all the possible answers cited on a line by line basis) essentially smashes the answers table and the Patient answers table together into one confused table with ptID, questID and answerID all in it.

    if i understand your previous post(s) correctly, I need to create the form in such a way that only a right/useful answer is possible.

    But if there's a translation to be made between the answers returned by the option group (1, 2, 3) and all the question-specific possible answers that those values might represent ( 1= Yes, 1= Excellent, 1= Bob) then I smell another table hiding somewhere (?). Or is that generated as part of the option groups wizard?

    I just trucked up to campus to get to a computer that has that wizard installed (darn Win2k cd is in a box 2300mi away at the moment) so I'll give that a whirl.

    thanks again

  13. #13
    Join Date
    Apr 2004
    Posts
    6

    might as well share

    heck, here's the current version if anyone cares to look.

    (its ok, you can laugh

  14. #14
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Nah you dont smell another table. I was saying that so you could like maybe have a printed list.

    Yes/No/Maybe Questions Yes = 1, No = 2, Maybe = 3

    So when you look at your table/data you will be able to tell more of what their answer was before you translate that into something else (I am sure you will wanna make a report displaying questions and then the answers).

    Excellent/Good/Not Well E = 1, G = 2, N = 3 and so on and so forth. Yes and no questions you would want with 1 always being either yes or no your pick. I am just suggesting that because of organizational purposes.

    Hope this helps,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  15. #15
    Join Date
    Apr 2004
    Posts
    6
    Originally posted by JSThePatriot
    Nah you dont smell another table. I was saying that so you could like maybe have a printed list.

    Yes/No/Maybe Questions Yes = 1, No = 2, Maybe = 3

    So when you look at your table/data you will be able to tell more of what their answer was before you translate that into something else (I am sure you will wanna make a report displaying questions and then the answers).

    Excellent/Good/Not Well E = 1, G = 2, N = 3 and so on and so forth. Yes and no questions you would want with 1 always being either yes or no your pick. I am just suggesting that because of organizational purposes.

    Hope this helps,
    JS
    hmm... i'm gradually getting this

    Right now, though, I'm able to create nice little option groups using the wizard in Forms, but the wizard never asks me where I want to store the results of the option group. So i have an option group that isn't connected to anything.

    (i'm running under the assumption that the whole viability of separating the "answers" from the "patient answers" was that the option groups would populate the "patient answers" with the potential answers provided by the "answers" table. I like this logic, but the option group wizard isn't bridging that gap for me.)

    Not to be outdone, I went searching for more info, and found one source that at least implies that the wizard should be helping me with this.

    http://www.uic.edu/depts/accc/semina...ms-option.html

    What am i missing?

    thanks!

    EDIT: acutally, the patient answers table is being populated by the info that I'm hand keying into the option group, right? Otherwise, I'm doing tons of re-keying that the Lookup wizard might be preventing).

Posting Permissions

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