Table design help!
in [Tables]
|
Prev: setvalue macro
Next: Nested combo box
From: Hannah on 17 Nov 2009 05:02 Fred - first off thank you for being so patient with me! I really do appreciate all your help. In answer to your questions: 1. It's a bit difficult because I have so many different types of question (yes/nos, multiple choice, multiple choice where they can choose more than one answer or add their own answer, and open questions where they can write whatever they want). The questionnaire is not designed brilliantly (don't even get me started on that) so it's pretty difficult for any question to make a list of allowable answers as people have written a whole bunch of things. So I suppose what I'm saying is no, it's probably very rare that I could anticipate and tell the database what the allowable answers are for each question. 2. I think I do want it to know the "if then" linkages if that's possible? 3. No, it doesn't have to guide the person answering them. It'll be me inputting the data and I quite honestly have dreams/nightmares about it so I'm pretty sure I know inside out what comes next! 4. No, the participants will only take one survey. There are several fields with information on the person. 5. No, I don't have a real world numbering system for the people. I'm not worried about tracking the people (beyond being able to link them to their questionnaire) so I hadn't thought about a numbering system. 6. There isn't a naming/numbering system for the questionnaires as such. There are eleven different types of questionnaire (so I suppose I have names for the category of questionnaire) but I hadn't made a filing system for them yet. Our plan had been to number and file them according to the numbers used in Access. 7. The questions are numbered in real life but badly (some follow up questions don't have a number). It isn't unique across all questions or even the questionnaire itself (some questionnaires have 3 parts each with questions 1,2,3). I hope that's helped answer your questions and apologies if I've given long winded/unclear answers to anything. Thanks again for all your help. "Fred" wrote: > > Also: > > 5. Do you already have a real-world numbering system for people (Like > PersonIDNumber) > > 6. Do your already have a real world naming or numbering system for the > questionaires? > > 7. Do you have a real world numbering system fo the questions. Is it > unique across ALL questions, oor just across the questions within on > questionaire? > >
From: Fred on 17 Nov 2009 08:48 Hannah, Great answers. Answering your one question on #2, anythign is possible. Now, is it feasible for you......answer is also yes if you are just recording, printing and displaying.....I.E. not having Access do anything fancy based on that. I'm going to assume an answer to a question which I forgot to ask: Is a particular question usually used just for one survey? I will assume "yes". If you want to use it for more than one survey you will just re-enter it as "another" questions. This avoids the complexity of databasing a many-to-many relationship Again, the response would be a bood rather than a post, bust here's my guess at a framework. Shorten my overly long explanatory table and field names. Add fields as desired with info on the entity (with one-to-one correspondence to that entity) which is databased in the table. Make a table "Questionaires" with fields: -QuestionaireNumber (Autonumber, Primary Key (PK) -QuestionaireNameOrDescription (text) Make a table" InstancesOfSomeoneTakingOfASurvey with fields: -SurveyInstanceNumber (Autonumber, Primary Key (PK) -QuestionaireNumber Integer, not autonumber, this will be a Foreign Key (FK) Note: FK is a use, not a setting. The subject number will be put into this field. -Fields for name and all desired info on the person who took it Make a table "Questions" with the following fields: QuestionNumber (Autonumber, PK) (use these numbers in your paper forms....if not possible, make another field to store the question number from the paper form) QuestionText QuestionAireNumber (integer) This is a FK which will take record which questionaire the question is for Maka a table "PossibleAnswers" wiht these fields QuestionNumber (insert the number of the question that the answer is applicable to AnswerNumber (Autonumber, PK) Answer (this holds any desired answers like "a", "b", "Yes" "No" and maybe the entire text of answers if you wish) Answer Description Description of the answers if not contained in the text. For example, if an answer is "a", this says that taht means "I prefer green" FollowUpQuestionNumber When this answer lead to a perticular next question, record that questions number here Make a table "InstancesOfAQuestionBeingAnswered" with these fields: "AnswerInstanceNumber" Autonumber, PK. "QuestionNumber" "Answer" For now, just manually enter the answer. Later, get someone whoc is smarter than me to tell you how to make a dropdown that shows th epossible answers specific to that question) "SurveyInstanceNumber" Well, that's just shooting form the hip (but hopefull a good structure as a foundation)
From: Hannah on 18 Nov 2009 04:19 Thanks Fred - you've given me a lot to think about and I'm feeling much more happy about approaching it. Thanks again, can't tell you how much I appreciate it. "Fred" wrote: > Hannah, > > Great answers. > > Answering your one question on #2, anythign is possible. Now, is it > feasible for you......answer is also yes if you are just recording, printing > and displaying.....I.E. not having Access do anything fancy based on that. > > I'm going to assume an answer to a question which I forgot to ask: Is a > particular question usually used just for one survey? I will assume "yes". > If you want to use it for more than one survey you will just re-enter it as > "another" questions. This avoids the complexity of databasing a many-to-many > relationship > > Again, the response would be a bood rather than a post, bust here's my guess > at a framework. > > Shorten my overly long explanatory table and field names. Add fields as > desired with info on the entity (with one-to-one correspondence to that > entity) which is databased in the table. > > Make a table "Questionaires" with fields: > > -QuestionaireNumber (Autonumber, Primary Key (PK) > -QuestionaireNameOrDescription (text) > > > > Make a table" InstancesOfSomeoneTakingOfASurvey with fields: > > -SurveyInstanceNumber (Autonumber, Primary Key (PK) > -QuestionaireNumber Integer, not autonumber, this will be a Foreign Key > (FK) Note: FK is a use, not a setting. The subject number will be put > into this field. > -Fields for name and all desired info on the person who took it > > Make a table "Questions" with the following fields: > > QuestionNumber (Autonumber, PK) (use these numbers in your paper forms....if > not possible, make another field to store the question number from the paper > form) > QuestionText > QuestionAireNumber (integer) This is a FK which will take record which > questionaire the question is for > > Maka a table "PossibleAnswers" wiht these fields > QuestionNumber (insert the number of the question that the answer is > applicable to > AnswerNumber (Autonumber, PK) > Answer (this holds any desired answers like "a", "b", "Yes" "No" and maybe > the entire text of answers if you wish) > Answer Description Description of the answers if not contained in the > text. For example, if an answer is "a", this says that taht means "I prefer > green" > FollowUpQuestionNumber When this answer lead to a perticular next > question, record that questions number here > > > Make a table "InstancesOfAQuestionBeingAnswered" with these fields: > > "AnswerInstanceNumber" Autonumber, PK. > "QuestionNumber" > "Answer" For now, just manually enter the answer. Later, get someone whoc > is smarter than me to tell you how to make a dropdown that shows th epossible > answers specific to that question) > "SurveyInstanceNumber" > > Well, that's just shooting form the hip (but hopefull a good structure as a > foundation) > > > > > > > > > > > > > > > > > > > > > > > > > > >
From: Fred on 18 Nov 2009 08:10
Hello Hannah, Happy to help. I wrote and posted it too fast for it's size, but hopefully the numerous typos are obvious / not obscuring anything, Good luck on your Access adventure. Fred |