Creating multivalue field
- Thread starter samia
- Start date
- Local time
- Yesterday, 22:54
- Joined
- Feb 28, 2001
- Messages
- 30,566
If by this you mean that you want to keep multiple answers in the same record, you can't without violating normalization.
Even WITH violating normalization, you are asking for more trouble than you could ever imagine.
Rethink your question to be more specific as to the need/reason for what you wanted. We might be able to suggest a solution or explain why you are going in a totally wrong direction.
Even WITH violating normalization, you are asking for more trouble than you could ever imagine.
Rethink your question to be more specific as to the need/reason for what you wanted. We might be able to suggest a solution or explain why you are going in a totally wrong direction.
neileg
AWF VIP
- Local time
- Today, 04:54
- Joined
- Dec 4, 2002
- Messages
- 5,970
Do you mean that you want a field to record one of a limited choice of answers? Eg pick one of 5 different answers?
If this is so, I'd use a separate table with the answers in and a junction table to join this to the user and question. The handling of the data would need to be done in a form, not at table level.
If this is so, I'd use a separate table with the answers in and a junction table to join this to the user and question. The handling of the data would need to be done in a form, not at table level.
Thanks Guys,
I sincerely apologise for the not so clear question. What I was asking is say you have a question: What is the role of ICT unit; possible answers are; To set guidelines, to approve bids, to approve budgets etc etc. So a person could answer any two or all three. My question is (I've done it for scietific data in Epi Info) to have what is the role as the main or group question with each of the other as variables grouped to what is the role. Hope this is clear,
Thanks in advance,
Samia
I sincerely apologise for the not so clear question. What I was asking is say you have a question: What is the role of ICT unit; possible answers are; To set guidelines, to approve bids, to approve budgets etc etc. So a person could answer any two or all three. My question is (I've done it for scietific data in Epi Info) to have what is the role as the main or group question with each of the other as variables grouped to what is the role. Hope this is clear,
Thanks in advance,
Samia
Compressor
Registered User.
- Local time
- Today, 05:54
- Joined
- Sep 23, 2006
- Messages
- 118
If you want it in one table or field per se:
Possible answers are:
A. To set guidelines
B. To approve bids
C. To approve budgets
D. To set guidelines, to approve bids, to approve budgets
E. to set guidelines, to approve bids
etc etc...
or just make multiple fields....
Question 1 will have.... answer A, B, C, D, E, etc. There
Question 1 in Qtable
Q1Answer1 in Answertable
Q1Answer2 in answertable
Q1Answer3 in answertable
etc.
In a form you can then let everybody select one of the available answers different fields, using 1 lookup table with the possible answers in it.
Or.... I don't get your question. But otherwise, that would be the way to go I think...
If not.... its gonna get crazy anyway, one question with a possible combination of 5 different answers... then you will need to make pretty long lists in a lookup table.
Possible answers are:
A. To set guidelines
B. To approve bids
C. To approve budgets
D. To set guidelines, to approve bids, to approve budgets
E. to set guidelines, to approve bids
etc etc...
or just make multiple fields....
Question 1 will have.... answer A, B, C, D, E, etc. There
Question 1 in Qtable
Q1Answer1 in Answertable
Q1Answer2 in answertable
Q1Answer3 in answertable
etc.
In a form you can then let everybody select one of the available answers different fields, using 1 lookup table with the possible answers in it.
Or.... I don't get your question. But otherwise, that would be the way to go I think...
If not.... its gonna get crazy anyway, one question with a possible combination of 5 different answers... then you will need to make pretty long lists in a lookup table.
boblarson
Smeghead
- Local time
- Yesterday, 20:54
- Joined
- Jan 12, 2001
- Messages
- 32,040
No, the key thing is to design your database with the CORRECT number of tables to do the job properly. KeithG's answer is the way you want to approach it. If you need to have multiple answers to a question then a table for the questions and then a separate table with the answers in it would be the way to go. You just store the question ID, personID, and answer in the answers table. To put it on a form, might require you to use a subform under the questions, that way you can add multiple answers to a single question and easily store the answers in the answers table.
- Local time
- Yesterday, 22:54
- Joined
- Feb 28, 2001
- Messages
- 30,566
Actually, the "questionnaire" question has come up before. Search the forum for keywords "Questionnaire" and "Survey" - but watch out for folks who have posted survey-type questions on topics not actually related to the word Survey.
You have to consider several tables.
1. The questions go by themselves in one table - and each question has its own unique QuestionID that is the prime key for the table.
2. The possible answers go in a separate table - and each answer has TWO keys. One is the QuestionID to which it applies. The other is the answer-number for that question. This has two possible solutions.
2.a If the same answer occurs for more than one question, you might wish to duplicate the answers, once for each question that has that possible answer. If duplicate answers are rare, this might be the way to go.
2.b OR... you can have a "master answer" table (which makes sense if you have LOTS of repetition of answers) and a junction table between the question and the condoned answers. Then you would have one entry in the junction table showing a permitted question/answer pair.
3. To actually process someone's answers, you need one or two more tables. If you retain the identity or other demographic information about the people who answer these questions, you need a "people" table with a person ID number. If not, then you don't need a separate table.
4. For a completed question/answer series, you need a table that contains a questionnaire ID (which could be a Person ID or just a questionnaire sequence number.) This table includes the date on which the questions were answered and other information. (Complication to be considered - if a person can take the survey more than once, this MUST be a separate table and you can't "sneak" it into the person table.)
5. One last junction table - a list of questions and the answers given for that particular survey. This one requires special consideration. You only need three fields in this table. 5.1 - Person/Survey ID #; 5.2 - Question number; 5.3 - Answer number.
Note that of these three fields, The Survey ID and Question ID are candidates for a compound prime key. Despite the fact that the answer number must match a condoned answer for the give question, the answer ID is at MOST a foreign key. That is because you would not have multiple records in the table where the same question got two different answers from the same survey or questionnaire. So in this case, the answer ID is dependent, not independent data.
One last piece, optional, for cases where you have more than one questionnaire or where you mix/match questions chosen by some selection criteria:
SurveyStyle: - a table with a style number and descriptive material for same and a QStyleList, a list of style number/question number pairs, showing that questionnaire 123 has a given list of questions while #124 might have totally different questions with no overlap.
You have to consider several tables.
1. The questions go by themselves in one table - and each question has its own unique QuestionID that is the prime key for the table.
2. The possible answers go in a separate table - and each answer has TWO keys. One is the QuestionID to which it applies. The other is the answer-number for that question. This has two possible solutions.
2.a If the same answer occurs for more than one question, you might wish to duplicate the answers, once for each question that has that possible answer. If duplicate answers are rare, this might be the way to go.
2.b OR... you can have a "master answer" table (which makes sense if you have LOTS of repetition of answers) and a junction table between the question and the condoned answers. Then you would have one entry in the junction table showing a permitted question/answer pair.
3. To actually process someone's answers, you need one or two more tables. If you retain the identity or other demographic information about the people who answer these questions, you need a "people" table with a person ID number. If not, then you don't need a separate table.
4. For a completed question/answer series, you need a table that contains a questionnaire ID (which could be a Person ID or just a questionnaire sequence number.) This table includes the date on which the questions were answered and other information. (Complication to be considered - if a person can take the survey more than once, this MUST be a separate table and you can't "sneak" it into the person table.)
5. One last junction table - a list of questions and the answers given for that particular survey. This one requires special consideration. You only need three fields in this table. 5.1 - Person/Survey ID #; 5.2 - Question number; 5.3 - Answer number.
Note that of these three fields, The Survey ID and Question ID are candidates for a compound prime key. Despite the fact that the answer number must match a condoned answer for the give question, the answer ID is at MOST a foreign key. That is because you would not have multiple records in the table where the same question got two different answers from the same survey or questionnaire. So in this case, the answer ID is dependent, not independent data.
One last piece, optional, for cases where you have more than one questionnaire or where you mix/match questions chosen by some selection criteria:
SurveyStyle: - a table with a style number and descriptive material for same and a QStyleList, a list of style number/question number pairs, showing that questionnaire 123 has a given list of questions while #124 might have totally different questions with no overlap.
Similar threads
