1 2 3 Previous Next 132 Replies Latest reply on Dec 10, 2012 2:32 PM by RonJennings

    creating a database for survey data



      creating a database for survey data


      I started creating a database to use to analyze qualitative data gathered from a survey, and I have 4 tables.  I just looked at the sample survey FMP database that I was referred to on here and noticed that the way my responses table is set up is different than the sample.  I have my database set up so that each survey takes up one horizontal line - so if you go across the table from left to right you see all of a single respondent's answers.  But the sample has it set up so each answer is vertical.  So there are fewer columns on the sample but many more rows than mine.  Mine has one row per survey but many columsn.  Does that make sense?

      If so, I'm wondering if I need to change mine so that it resembles the sample, and if so, how I would do that?  When I export the data from the survey database it gomes out the way I have it.  I can't figure out how to switch it around without a lot of cutting and pasting.




        • 1. Re: creating a database for survey data

          The layout's design isn't really the issue. It's the structure of your table where you store your repsonses. It's much eaiser to display related records, where each response to a question is stored in a separate record, vertically than horizontally.

          Horizontal display of a set of related records can be done, but it requires a much more complicated layout design.

          In your case, we'd need to look at the table structure to your table of reponses to see if these are separate fields in the same record so that you have one record for each survey completed or one record for each survey question answered.

          • 2. Re: creating a database for survey data

            I have one record for each survey completed.

            I'm fine with a complicated layout.  I just want to make sure this does not mess up my ability to analyze the data.

            My plan is to import the responses to a bunch of open ended questions to FMP.  The responses for each question will be on a separate tab.  Included on that tab will be fields (up to 10 different fields) that will be used to code the survey responses.  Then I will need to be able to search across those fields to see how many times a certain code came up (codes will come from a drop-down list).

            I have the coded responses in a separate table too.

            Do you think this will work?

            • 3. Re: creating a database for survey data

              There's a big difference between layout design and table design.

              "Up to 10 different fields" sounds like you have 10 fields defined in one table where you should have 10 different records linked to a specific record in a related table. Putting each response to each question in a separate records will make analyzing the responses much simpler and more straight forward than putting all the responses to one survey in the same record.


              How have you defined your tables?

              How have you linked them in relationships?

              • 4. Re: creating a database for survey data

                You may have to script the analysis depending on exactly what you want to do. 

                How many tabs and does a question really have 10 fields of responses?

                This does seem a bit odd and as I said your analysis may require you to script the report output.

                If you can give us an example of a single Tab we may be able to have you avoid any landmines.


                • 5. Re: creating a database for survey data

                  This is to aamonddd (saw that response first.  Now I"m going to look at PhilModJunk's response.)

                  It looks like I will have 15 tabs. 

                  A single question could have up to 10 (maybe only 6-8) fields of codes because they are open-ended and some of the responses are LONG.  We have to capture everything they are saying.  Most will have more like 2-3 fields per question.

                  Let me give an example.  We ask the respondent to describe an organization.  If they list 10 different characteristics, then those different characteristics are coded in separate fields (as long as they are truly unique.  If they are all synonyms, then they would only be 1 field).  Most people will give 2-3 characteristics, but some people write a lot.  Does that make sense?

                  Thanks for the help.

                  • 6. Re: creating a database for survey data

                    PhilMod - I understand that you are saying it is easier to have each response to each question in a separate record (rather than one record per survey).  Is there any easy way to change an excel spreadsheet that has one record per survey into one record per question?  I have over 1500 responses, so I need any easy way to restructure the database.

                    I do understand the difference between layout and table design.  My idea was to have the layout set up with a different tab per open-ended question.  Then on that tab, users could see the response and selected the appropriate codes from the available fields.  There would be space for up to 10 (that is maximum.  probalby more like 6-8 would do) fields.

                    Here are my tables:

                    Respondents (User ID, survey status)

                    Survey Questions (Question ID, Question, QuestionName)

                    Survey Responses (UserID, the the rest of the fields are the QuestionNames.  There are about 20 of them.  This is where I realized I may have a problem.  There isn't a way to connect my Question names - which are field names to the QuestionName field in the "Survey Questions" table - so it doesn't connect to anything.)

                    Coded Responses (UserID, Description Type, Coded By, Created Date, DCharacteristics1, DCharacteristics2, etc. - and different labels for each of the coding fields).

                    I've spent a lot of time trying to figure out how to set this up properly.  Please let me know if I need to redo anything.  I would rather change it at this point and get it right than end up with a database that won't let us analyze the results.

                    • 7. Re: creating a database for survey data

                      There is a  way to translate your Excel into the various records even if its in a single row.

                      It sounds like you need to have your Response Codes to be set up a bit differently too. You want to have multiple response records to a single Survey Question Response Record. this will make analysis easier.

                      Sounds like a good overal structure would be

                      Survey ID (1 each survey type) >>>>Survey Question (One for each question per Survey) >>>>Survey Response (Multiple Records per question) >>>>Response Codes (Multiple Records per Response)

                      You can picture it kind of like a decendancy chart where Survey is the Parent the Questions are the Children Each Child can have multple (Grand Children and Each grand child multiple great grandchildren)

                      In order to get the data into the appropriate tables from the excel I would import the data into flat tables and then loop through that table parsing  the data into the appropriate records. the layouts can get complicated but not overly so.


                      • 8. Re: creating a database for survey data

                        This is starting to sound familiar Wink

                        There are ways to load columns of data into different records and if your spread sheet includes the questions in the first row, you can even get to the question names in order to establish links between questions and responses.

                        One simple, time consuming, but effective method is to import the same spreadsheet over and over but each time mapping a single column into the response field of your responses table. Immediately after import, your imported records form the found set of records for your current layout and you can use Replace Field Contents to update your questionID or a QuestionName field with a common value for the column just imported and now you can link the responses to records in the Questions table.

                        • 9. Re: creating a database for survey data

                          Yes, PhilModJunk, you helped me with my last project, but I had set up the database wrong, so I couldn't do what I wanted.  I"m trying to make sure I set it up correctly this time. 

                          Thanks for the responses.  I'll have to see if I can figure out what I need to do.  I may be back with more questions later, but I thinik you have both given me good tips for getting this set up properly- if only I can figure out how to do it...

                          • 10. Re: creating a database for survey data

                            Please note that this is not the only way to get the data into your table. It's fairly practical if you only need to do this once. If it's something you need to do over and over again, you may want to set up an approach with a script where you pull the data into a temporary table and then use a script to run throught the fields and records to move the data into your questions table.

                            • 11. Re: creating a database for survey data

                              PhilMod - I will only do it once for this project.  but I will have several more like this in the future.

                              Does this make sense for my table set up?:

                              Survey ID (userID, status - complete, incomplete)

                              Survey Questions (QuestionID, list of questions)

                              Survey Response (QuestionID, UserID, Response, ResponseID)

                              Response Code (ResponseID, Codes)

                              • 12. Re: creating a database for survey data

                                Survey Questions would be questionID, Question--not a list of questions as you would have one record for each question asked. If you ever needed to load more than one survey into your file, you might need a Survey ID field in all of the tables, including a new table called Survey. (I'd rename Survey ID as "Respondants" as this appears to be one record to a person responding to the survey.)

                                • 13. Re: creating a database for survey data

                                  See my additions


                                  Survey ID (userID, status - complete, incomplete) need a key field for Survey ID

                                  Survey Questions (QuestionID, list of questions) need to add Survey ID

                                  Survey Response (QuestionID, UserID, Response, ResponseID) need to ad survey ID (shouldnt need UserID as its in the Survey ID)

                                  Response Code (ResponseID, Codes) Should add Question ID and Generate A ResponsecodeID

                                  this would allow you to produce an indefinite number of response codes for each response for each question on the survey.

                                  When it comes to layout there is a trick to be able to do this all in a single layout. You may want a table of Global Fields to use as global display keys. By linking this global table to the Response Code you can select each response from a portal of responses and add codes as individual records.

                                  you can also use the display keys to show the responses to individual questions and navigate the whole survey through a single layout.


                                  • 14. Re: creating a database for survey data

                                    Thanks so much!  I think I get it enough to do some work.  I might be back later with more questions.  I appreciate the help!

                                    1 2 3 Previous Next