14 Replies Latest reply on Jan 25, 2011 11:21 AM by philmodjunk

    Survey Database Creation and Import from Excel Question

    BG_1

      Title

      Survey Database Creation and Import from Excel Question

      Post

      Hello all,

      I've run into a problem with a database I just created.  I need to compare pre- and post-tests, which were inputted into Excel.

      Tables:  Subjects, Answers, Questions

      Relationships:  Subjects <--ID#--> Answers, Answers <--QuestionID #--> Questions

      The problem that I ran into is that by using this format, each Question made it it's own record, not a field like in a previous database I created. Now, I can't import the questions from excel.  In other words, there are no fields to import Question 1 through Question X into.  Does anyone have any suggestions?

        • 1. Re: Survey Database Creation and Import from Excel Question
          philmodjunk

          How are the questions orgainzed in Excel? In rows or Columns?

          Are these just the Questions or are they the questions and the answers?

          • 2. Re: Survey Database Creation and Import from Excel Question
            BG_1

            The question numbers columns, with the answers being in that column in the rows below.   It basically looks likes this.

            ID #        Q1   Q2   Q3   Q4

            1111         1     3    4     1

            1112         3     4    2     3

            etc. 

            There are other things that may be unique to the "person", such as GPA,  etc., but all that stuff imported easiy (since those were created in  fields, while the the Question database had questions as their own  records, instead of just being a record with many fields).  I'll need to input the answers manually, they are not in excel. 

            • 3. Re: Survey Database Creation and Import from Excel Question
              philmodjunk

              You may have to import your data repeatedly. In each import, you import the ID# and then a different Q column into the database each time.

              If you can rearrange the data in your spreadsheet to swap rows for columns, there might be a way to use fewer imports.

              • 4. Re: Survey Database Creation and Import from Excel Question
                BG_1

                I'm not too terribly good at explaining my problem, but I'll try to clarify as much as possible.  While the ID # and supplemental information got easily imported into the first table (My "Survey" table), it did not work for the Answers.  Since there were no records "Survey" table, it just imported all of the data neatly.  However, it doesn't detect a place to import the Excel questions to, since the Answers table has a record for each question. 

                Put in words, I would have loved if the database would see an ID #, and use the relationship to the Answer table to fill it out.  But the Answer table having each question as a record and not a field, doesn't translate well (even though all other surveys seem to taket his format).  I suppose this could be because they recorded data directly to Filemaker, instead of having "old" data already on Excel.  

                Just to point out, Excel imported the data perfectly when everything was in one table and had a large amount of fields. 

                Thanks again!

                Edit:  I wrote this before I saw your response, so I'll try that. 

                • 5. Re: Survey Database Creation and Import from Excel Question
                  BG_1

                  Hmm, I'm not having much luck with importing.  I'm not really understanding what it should look like even when it's right.  By this I mean:  If each question is a record, then how are the answers going to be scored/stored?  There are going to be many duplicates of Question ID#s. 

                  I'm also not quite sure how to import my data with it knowing its Question ID#.  I mean, my excel data has labeled columbs labeling the question, but that data will go in Answer field, not QID# field...

                  How can I get it to recognize a column header as 2 separate "fields" so to speak? 


                  The way it is now, even if I can get my questions to import, it will just be about 412 records + the 73 pre-created records with QID#s.  Will I have to manually go through and assign them all their QID#s manually?

                  • 6. Re: Survey Database Creation and Import from Excel Question
                    philmodjunk

                    What does ID# identify? I've been assuming that that number identifies the individual completing this survey.

                    If so, then import 1 imports ID# into and ID field and the Q1 column into the Response Field. Import 2 imports ID# into the ID field and the Q2 column into the response field...

                    Naturally, you'll also need a third field to identify what question is being asked. Easiest way to handle that is to perform a Replace field Contents operation just after each import. Since each import pulls in responses all for the same question, you can use this tool to load a third field in this table for the found set of all your newly imported records with a value that identifies the question they belong to.

                    Since each record can now be linked to both an individual and to a question, you should be able to compute and compare responses amongst the records in this table, using relationships to access the actual question text and info about the individual who answered the question.

                    • 7. Re: Survey Database Creation and Import from Excel Question
                      BG_1

                      Correct, ID# identifies the individual.   I'll try that, thanks!

                      • 8. Re: Survey Database Creation and Import from Excel Question
                        BG_1

                        I think the problem with the import lies with has to do with me having a QID # and the Answers/Results.

                        I'm not really sure how I can change the format of the excel document, as I need Filemaker to recognize not only that the data in Excel Heading "1" outputs to "Answer/Results", and that question number (displayed as the header in Excel) needs to output to QID as well.

                        For example:

                        UID---(Extra Information)---1---2---3---4---5---6---

                        X        X X X X X X X X  X     X   X    X    X   X    X

                        Filemaker is only letting me input UID and "Answers/Results".  I need to Filemaker to recognize that the heading in each column refers to the QID# and the data underneath corresponds to the responses to that particular QID of that particular participant.  I can't really think of a way to sneak in an extra column/row to explicitley let Filemaker know that the header has meaning beyond what the data under it contains. 

                        • 9. Re: Survey Database Creation and Import from Excel Question
                          BG_1

                          Edit:  I figured out the Find/Replace feature.  I think Filemaker was just being touchy or something.

                          • 10. Re: Survey Database Creation and Import from Excel Question
                            philmodjunk

                            Replace Field Contents and Find/Replace are two different tools and have different capabilities. I'm suggesting using Replace Field Contents--which is found in the Records menu.

                            • 11. Re: Survey Database Creation and Import from Excel Question
                              philmodjunk

                              It's also occurred to me that you might also import the data into an intermediary table where you have fields for each response, but then a script moves the data from this table into the final table, breaking up the data into separate records as it does so. With such an import, you can also import the first row as data instead of field names and then the data in the first imported record will hold your questions.

                              • 12. Re: Survey Database Creation and Import from Excel Question
                                BG_1

                                Thanks for all the help, I was able to get all of the information into the database.  Now, I get to move on to analyzing the data.  But, before I can start I need to invert some answers (some answers simply had a "not" inserted into it, so the test didn't become too redundant).  I'm thinking I may need a script for that, because I can't think of any other way to do it. 

                                Changing the question is easy, since it is in its own table.  I change the question, and the questions are updated in my results table. 

                                I can use the find feature to find the question I want (say all results to Q3), but I'm not sure how to apply an inversion of the results.  A work-around that I think of is to tell Filemaker replace certain values with another.  For example:

                                Turn 6 into 1, 5 into 2, 4 into 3, 3 into 4, 2 into 5, and 1 into 6. 


                                Thank for the continued support!

                                • 13. Re: Survey Database Creation and Import from Excel Question
                                  BG_1

                                  I experimented with reports and sub-summaries to present the information in a more readable way, but I'm still thinking I need to have a calculation field that calculates the change from pre-test to post-tests. What's making this hard for me is that I don't really yet understand how to ask filemaker to pull an answer from

                                  Here's how I thought I'd do it.

                                  -If there are duplicate ID numbers, that means that those duplicates represent the accumulation of people who took both tests.

                                  -When there is a duplicate ID number, take the answer inputted from the earlier test and subtract the post test. 

                                  Put into more mathy terms:  If UID had a duplicate, then output the difference of "Answer" from the earler record (pre-tests are BIO 112, post-tests are BIO 113) and "Answer from the later record. 

                                  So, Filemaker would see that subject 1234 has 2 records with the same QID number, therefor it would take the difference in the "Answer" field of the two records. 

                                  My apologies for asking so many questions.  The data was presented in a way that seems to make it difficult to deal with. 

                                  Thanks!

                                  • 14. Re: Survey Database Creation and Import from Excel Question
                                    philmodjunk

                                    You've started a new thread for this question and I posted an answer there...