If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
huge problems with database deisgn for survey
I am very inexperienced at database design and am saving huge problems.
I am trying to construct a Db in Access 2000. The data is based on information from client files and questions, as well as dates of the questions. The time scale from an initial event is important and will be the subject of a query. My original design was wrong in that it had a field for each question, however this did make for a relatively easy build. I am now trying to do the right thing, but I cannot understand how I get the responses into into the database. I have tblCase CaseID autoPK tblQuestionsAsked QuestionsAskedID PK auto Description tblResults ResultsID PK auto QuestionsAskedID FK Results tblAnswerType ATID PK RID FK AnswerVelue (number, 1, 2 0) Text (yes,no, unknown) I have looked at AYS but found it too confusing, I don't have enough experience to know what I need from this. I'm sorry to be so basic, I hope that someone can provide me with a simple solution. |
#2
|
|||
|
|||
huge problems with database deisgn for survey
I believe your tblResults needs a foreign key relating back to tblCase.
Otherwise, how do you track which case record corresponds with the results? You might want to take a look at the survey results entry screen. There is a command button that runs an append query with a source of the tblQuestionsAsked and a target of tblResults. The CaseID is pulled from the main form. -- Duane Hookom Microsoft Access MVP "Desperate in Ontario" wrote: I am very inexperienced at database design and am saving huge problems. I am trying to construct a Db in Access 2000. The data is based on information from client files and questions, as well as dates of the questions. The time scale from an initial event is important and will be the subject of a query. My original design was wrong in that it had a field for each question, however this did make for a relatively easy build. I am now trying to do the right thing, but I cannot understand how I get the responses into into the database. I have tblCase CaseID autoPK tblQuestionsAsked QuestionsAskedID PK auto Description tblResults ResultsID PK auto QuestionsAskedID FK Results tblAnswerType ATID PK RID FK AnswerVelue (number, 1, 2 0) Text (yes,no, unknown) I have looked at AYS but found it too confusing, I don't have enough experience to know what I need from this. I'm sorry to be so basic, I hope that someone can provide me with a simple solution. |
#3
|
|||
|
|||
huge problems with database deisgn for survey
Thanks for replying Duane,
I have added CaseID and I think I have made a command button using the append data query. I cant try it as ai havent entered any data yet. I need a form now. How do I make a form to enter the results? Do i enter text boxes and then options boxes to hold the answers? I tried making a table of possible answers, but my yes.no.unknown answers will need a number input for analysis. Thank you for your time. "Duane Hookom" wrote: I believe your tblResults needs a foreign key relating back to tblCase. Otherwise, how do you track which case record corresponds with the results? You might want to take a look at the survey results entry screen. There is a command button that runs an append query with a source of the tblQuestionsAsked and a target of tblResults. The CaseID is pulled from the main form. -- Duane Hookom Microsoft Access MVP "Desperate in Ontario" wrote: I am very inexperienced at database design and am saving huge problems. I am trying to construct a Db in Access 2000. The data is based on information from client files and questions, as well as dates of the questions. The time scale from an initial event is important and will be the subject of a query. My original design was wrong in that it had a field for each question, however this did make for a relatively easy build. I am now trying to do the right thing, but I cannot understand how I get the responses into into the database. I have tblCase CaseID autoPK tblQuestionsAsked QuestionsAskedID PK auto Description tblResults ResultsID PK auto QuestionsAskedID FK Results tblAnswerType ATID PK RID FK AnswerVelue (number, 1, 2 0) Text (yes,no, unknown) I have looked at AYS but found it too confusing, I don't have enough experience to know what I need from this. I'm sorry to be so basic, I hope that someone can provide me with a simple solution. |
#4
|
|||
|
|||
huge problems with database deisgn for survey
If your question are associated/related to a Case then you would have a main
form based on tblCase and a subform based on tblResults. The link master/child would be set to the CaseID field. A command button on the main form would run the append query. Is your Results field numeric or text? What do you want to store? You can always group by Results to count the number of like answers. -- Duane Hookom Microsoft Access MVP "Desperate in Ontario" wrote: Thanks for replying Duane, I have added CaseID and I think I have made a command button using the append data query. I cant try it as ai havent entered any data yet. I need a form now. How do I make a form to enter the results? Do i enter text boxes and then options boxes to hold the answers? I tried making a table of possible answers, but my yes.no.unknown answers will need a number input for analysis. Thank you for your time. "Duane Hookom" wrote: I believe your tblResults needs a foreign key relating back to tblCase. Otherwise, how do you track which case record corresponds with the results? You might want to take a look at the survey results entry screen. There is a command button that runs an append query with a source of the tblQuestionsAsked and a target of tblResults. The CaseID is pulled from the main form. -- Duane Hookom Microsoft Access MVP "Desperate in Ontario" wrote: I am very inexperienced at database design and am saving huge problems. I am trying to construct a Db in Access 2000. The data is based on information from client files and questions, as well as dates of the questions. The time scale from an initial event is important and will be the subject of a query. My original design was wrong in that it had a field for each question, however this did make for a relatively easy build. I am now trying to do the right thing, but I cannot understand how I get the responses into into the database. I have tblCase CaseID autoPK tblQuestionsAsked QuestionsAskedID PK auto Description tblResults ResultsID PK auto QuestionsAskedID FK Results tblAnswerType ATID PK RID FK AnswerVelue (number, 1, 2 0) Text (yes,no, unknown) I have looked at AYS but found it too confusing, I don't have enough experience to know what I need from this. I'm sorry to be so basic, I hope that someone can provide me with a simple solution. |
#5
|
|||
|
|||
huge problems with database deisgn for survey
I am at home now so will try that when I am back at work at the end of the week. My results will be a mixture of scores, yes/no/unknown, age ranges, education ranges and dates. I had problems grouping by results before but that may be because of my table structure which was more spreadsheet than access tall and thin. Thank you again "Duane Hookom" wrote: If your question are associated/related to a Case then you would have a main form based on tblCase and a subform based on tblResults. The link master/child would be set to the CaseID field. A command button on the main form would run the append query. Is your Results field numeric or text? What do you want to store? You can always group by Results to count the number of like answers. -- Duane Hookom Microsoft Access MVP "Desperate in Ontario" wrote: Thanks for replying Duane, I have added CaseID and I think I have made a command button using the append data query. I cant try it as ai havent entered any data yet. I need a form now. How do I make a form to enter the results? Do i enter text boxes and then options boxes to hold the answers? I tried making a table of possible answers, but my yes.no.unknown answers will need a number input for analysis. Thank you for your time. "Duane Hookom" wrote: I believe your tblResults needs a foreign key relating back to tblCase. Otherwise, how do you track which case record corresponds with the results? You might want to take a look at the survey results entry screen. There is a command button that runs an append query with a source of the tblQuestionsAsked and a target of tblResults. The CaseID is pulled from the main form. -- Duane Hookom Microsoft Access MVP "Desperate in Ontario" wrote: I am very inexperienced at database design and am saving huge problems. I am trying to construct a Db in Access 2000. The data is based on information from client files and questions, as well as dates of the questions. The time scale from an initial event is important and will be the subject of a query. My original design was wrong in that it had a field for each question, however this did make for a relatively easy build. I am now trying to do the right thing, but I cannot understand how I get the responses into into the database. I have tblCase CaseID autoPK tblQuestionsAsked QuestionsAskedID PK auto Description tblResults ResultsID PK auto QuestionsAskedID FK Results tblAnswerType ATID PK RID FK AnswerVelue (number, 1, 2 0) Text (yes,no, unknown) I have looked at AYS but found it too confusing, I don't have enough experience to know what I need from this. I'm sorry to be so basic, I hope that someone can provide me with a simple solution. |
#6
|
|||
|
|||
huge problems with database deisgn for survey
Hi, I have set up the forms and am trying to add the boxes for the results. Should I be looking at list boxes or combo boxes? My questions are listed in rows as text in tblQuestionsAsked, not as fields, so how do I enter each question into the form and have a box for the results? Should my results table have rows for each answer? Would it be so terrible if I went back to having a field for each question?? |
#7
|
|||
|
|||
huge problems with database deisgn for survey
On Thu, 13 Nov 2008 07:17:01 -0800, Desperate in Ontario
wrote: Hi, I have set up the forms and am trying to add the boxes for the results. Should I be looking at list boxes or combo boxes? Whichever is more suitable for your user interface. They both let you edit fields; they look different (the listbox always displays multiple rows, the combo drops down thereby saving screen space, and they handle keystrokes differently). Both work. My questions are listed in rows as text in tblQuestionsAsked, not as fields, so how do I enter each question into the form and have a box for the results? You don't enter questions INTO THE FORM. You *use* the form (or a Questions form) to enter questions INTO THE TABLE. The form for entering answers displays the data stored in the table. Should my results table have rows for each answer? Yes. Would it be so terrible if I went back to having a field for each question?? Yes. In the nature of surveys, questions change over time. If you have a field per question, then when you add or change a question, you must restructure your Table, restructure all Queries involving the table, redesign your Form, redesign your Reports... a monstrous amount of work. In a normalized design when you change or add a question you open the Questions form, change or add the question... and you're DONE. If you'll only ever do *one* survey, and never change it, and discard the database when you're done, then maybe you can do it that way... but it might be simpler to do so in Excel, since you're essentially building a spreadsheet rather than a database. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
huge problems with database deisgn for survey
I agree with John (as usual). One of the nice benefits of creating a
normalized survey application is you don't have to create lots of fields, lookup tables, controls, etc. When I began working on the At Your Survey solution, I was taking over an application started by someone else. I very soon got tired of adding fields, adding tables, adding controls, adding code, adding logic,... While the normalized structure requires that your survey conforms with some basic constraints, it is quite flexible. It could be modified to provide a different user interface but the table structure should remain mostly the same. -- Duane Hookom Microsoft Access MVP "John W. Vinson" wrote: On Thu, 13 Nov 2008 07:17:01 -0800, Desperate in Ontario wrote: Hi, I have set up the forms and am trying to add the boxes for the results. Should I be looking at list boxes or combo boxes? Whichever is more suitable for your user interface. They both let you edit fields; they look different (the listbox always displays multiple rows, the combo drops down thereby saving screen space, and they handle keystrokes differently). Both work. My questions are listed in rows as text in tblQuestionsAsked, not as fields, so how do I enter each question into the form and have a box for the results? You don't enter questions INTO THE FORM. You *use* the form (or a Questions form) to enter questions INTO THE TABLE. The form for entering answers displays the data stored in the table. Should my results table have rows for each answer? Yes. Would it be so terrible if I went back to having a field for each question?? Yes. In the nature of surveys, questions change over time. If you have a field per question, then when you add or change a question, you must restructure your Table, restructure all Queries involving the table, redesign your Form, redesign your Reports... a monstrous amount of work. In a normalized design when you change or add a question you open the Questions form, change or add the question... and you're DONE. If you'll only ever do *one* survey, and never change it, and discard the database when you're done, then maybe you can do it that way... but it might be simpler to do so in Excel, since you're essentially building a spreadsheet rather than a database. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
huge problems with database deisgn for survey
Thanks for the replies
Excel won't provide the answers though as it won't porvide reports in the same way as Access does. I am probably wrong there but my experience of Excel doesnt give me means of saying that those Cases who live in post code xxx, who are aged xx, answered, yes, scored 20 etc. My basic problem with this is that now I have the tables. I haven't been able to construct a form that has all the questions (i realize that I can get round this by using text boxes,) and a method of entering the answers so that the results table is populated. The two local "Experts" suggest that I use a field per question. Even some online examples use this method, So I am very confused. I really want to do this the right way tho, I don't want to build in problems for later, I have made a query that is ResultsToQuestionText. I thought this might help with the forms as a subform to case. i still have the problem of how to get a box for each response and show which question. I have tried substituting my questions into AYS but that was a mess too! thank you "Duane Hookom" wrote: I agree with John (as usual). One of the nice benefits of creating a normalized survey application is you don't have to create lots of fields, lookup tables, controls, etc. When I began working on the At Your Survey solution, I was taking over an application started by someone else. I very soon got tired of adding fields, adding tables, adding controls, adding code, adding logic,... While the normalized structure requires that your survey conforms with some basic constraints, it is quite flexible. It could be modified to provide a different user interface but the table structure should remain mostly the same. -- Duane Hookom Microsoft Access MVP "John W. Vinson" wrote: On Thu, 13 Nov 2008 07:17:01 -0800, Desperate in Ontario wrote: Hi, I have set up the forms and am trying to add the boxes for the results. Should I be looking at list boxes or combo boxes? Whichever is more suitable for your user interface. They both let you edit fields; they look different (the listbox always displays multiple rows, the combo drops down thereby saving screen space, and they handle keystrokes differently). Both work. My questions are listed in rows as text in tblQuestionsAsked, not as fields, so how do I enter each question into the form and have a box for the results? You don't enter questions INTO THE FORM. You *use* the form (or a Questions form) to enter questions INTO THE TABLE. The form for entering answers displays the data stored in the table. Should my results table have rows for each answer? Yes. Would it be so terrible if I went back to having a field for each question?? Yes. In the nature of surveys, questions change over time. If you have a field per question, then when you add or change a question, you must restructure your Table, restructure all Queries involving the table, redesign your Form, redesign your Reports... a monstrous amount of work. In a normalized design when you change or add a question you open the Questions form, change or add the question... and you're DONE. If you'll only ever do *one* survey, and never change it, and discard the database when you're done, then maybe you can do it that way... but it might be simpler to do so in Excel, since you're essentially building a spreadsheet rather than a database. -- John W. Vinson [MVP] |
#10
|
|||
|
|||
huge problems with database deisgn for survey
To get the results, you need to run an append query as per two previous
replies in this thread. The questions are then displayed in a continuous subform. I'm not sure if it would help understand but if you look at the Northwind Orders and OrderDetails tables. Let's make a couple assumptions: - an order record is like a case record - an order detail is like a result - a product is like a question (let's assume there are only 7 products available or 7 questions) - we then have to assume that everyone is going to buy all seven products (answer all 7 questions) == northwind == You would create an append query based on your product table and the one Order from your order table. This would create 7 order detail records waiting for you to enter quantities to purchase. == survey == You would create an append query based on your questions table and the one Case from your tblCase table. This would create 7 tblResults records waiting for you to enter an answer value. You would never create a single field for each product to be ordered. For instance you wouldn't have an Orders table with fields like: Bread, Milk, Eggs, Apples, ... As I stated earlier, if your questions are fairly generic, you need to normalize. Maybe you should just allow the two local "Experts" create all the fields, controls, reports, queries, ... required with the multiple field solution ;-) -- Duane Hookom Microsoft Access MVP "Desperate in Ontario" wrote: Thanks for the replies Excel won't provide the answers though as it won't porvide reports in the same way as Access does. I am probably wrong there but my experience of Excel doesnt give me means of saying that those Cases who live in post code xxx, who are aged xx, answered, yes, scored 20 etc. My basic problem with this is that now I have the tables. I haven't been able to construct a form that has all the questions (i realize that I can get round this by using text boxes,) and a method of entering the answers so that the results table is populated. The two local "Experts" suggest that I use a field per question. Even some online examples use this method, So I am very confused. I really want to do this the right way tho, I don't want to build in problems for later, I have made a query that is ResultsToQuestionText. I thought this might help with the forms as a subform to case. i still have the problem of how to get a box for each response and show which question. I have tried substituting my questions into AYS but that was a mess too! thank you "Duane Hookom" wrote: I agree with John (as usual). One of the nice benefits of creating a normalized survey application is you don't have to create lots of fields, lookup tables, controls, etc. When I began working on the At Your Survey solution, I was taking over an application started by someone else. I very soon got tired of adding fields, adding tables, adding controls, adding code, adding logic,... While the normalized structure requires that your survey conforms with some basic constraints, it is quite flexible. It could be modified to provide a different user interface but the table structure should remain mostly the same. -- Duane Hookom Microsoft Access MVP "John W. Vinson" wrote: On Thu, 13 Nov 2008 07:17:01 -0800, Desperate in Ontario wrote: Hi, I have set up the forms and am trying to add the boxes for the results. Should I be looking at list boxes or combo boxes? Whichever is more suitable for your user interface. They both let you edit fields; they look different (the listbox always displays multiple rows, the combo drops down thereby saving screen space, and they handle keystrokes differently). Both work. My questions are listed in rows as text in tblQuestionsAsked, not as fields, so how do I enter each question into the form and have a box for the results? You don't enter questions INTO THE FORM. You *use* the form (or a Questions form) to enter questions INTO THE TABLE. The form for entering answers displays the data stored in the table. Should my results table have rows for each answer? Yes. Would it be so terrible if I went back to having a field for each question?? Yes. In the nature of surveys, questions change over time. If you have a field per question, then when you add or change a question, you must restructure your Table, restructure all Queries involving the table, redesign your Form, redesign your Reports... a monstrous amount of work. In a normalized design when you change or add a question you open the Questions form, change or add the question... and you're DONE. If you'll only ever do *one* survey, and never change it, and discard the database when you're done, then maybe you can do it that way... but it might be simpler to do so in Excel, since you're essentially building a spreadsheet rather than a database. -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|