Need help designing a survey database
I am brand new to FMP, although have gone through the provided tutorials and training from FM, so am doing best I can to learn. I have also searched extensively on the forum website to understand how to set up my database, and have a few key quesitons about how to structure my tables before I even begin layouts.
My task: design a database where several students (including myself) can enter the data from a survey that was completed on paper. I know I want a layout where a student can enter all the responses from each survey on one page, with either radion buttons or drop down menus to select the appropraite answers. The selected answers need to be linked to a numerical code which will be stored in a response table. because my responses will be exported to excel, then entered into a statistical software for detailed analysis.
Based on what I have gleaned from this forum, I have designed 5 tables, with the following fields/records as below:
Table 1: Surveys (fields: surveyID, State, Date administered). This has three feilds and 6 records (I have 6 survey versions)
Table 2: Respondents (fields: _pkID#, coded by, date received, date entered). 4 fields, 700 records (I have 700 completed surveys)
Table 3: Questions (fields: _pkQuestionID, _fkID#, Text). 3 fields, 100 records ( I have 100 questions)
Table 4: Responses... This is where I am having difficulties. Based on other responses, I am thinking I need each record to be the question , and each field to be a unique respondents answer to that question - i.e. - 100 records, 700 fields. This does not seem intuituve to me - it seems more intuitive to have the records be the individual survey taker, and the fields be each individual question - 700 rows, 100 columns. Help?
Table 5: Code Book... again, I am confused here. Each question has multiple responses - like, dislike, neutral; agree, disagree, neutral; always, often, sometimes, rarely, never....etc. I need each of these words to correspond with a number that can be used in my statistical software. Do I make a single code book table with each question, and each possible reponse, with a corresponding number? If I do this, I am realy not sure how to set up the recods and fields, because each question has a different set of responses.
Or do I need a separate table per question? Seems that a separate table per question is easy, I don't really understand how to set up just one codebook that has each of the 100 questions and possible responses in it (with words and numbers for my menus), but an individual table per question seems silly - a lot of tables.
Any help is appreciated. I really want to set this up correctly before I start my layouts. This is a one-time use project for my advisor...