1 2 Previous Next 23 Replies Latest reply on Mar 11, 2011 8:27 AM by philmodjunk

    Number of Tables for Test



      Number of Tables for Test


      Hello all,

      I'm working on my second database and I have the basic framework set up.  Before I get into the nitty-gritty, I'd like to make sure I have a good foundation. 

      The intention of this database is to allow easier analysis of tests taken by students.  Specfically, I would like to assign values to their responses.  I've only done a questionairre database, so I don't know yet how to attach a value (0/2 points, 1/2 points, 2/2 points, for example) to their answers.  Their end grade is less important than their specific answers, as the answers are used to detect common traps that students fall into when using logic to solve a problem. 

      The database has these tables so far:

      1. Student Information (First Name, Last Name, ID#)
      2. Question Bank (QID#)
      3. Student Responses (Responses)
      4. Answer / Value Bank (has the answer prompts and the values/scores for each answer)

      Relationships are:

      1. Student Info <--ID-->Student Responses
      2. Student Responses <--QID--> Question Bank
      3. Question Bank <--QID--> Answer / Value Bank

      I guess where I'm a bit confused is whether or not I need separate tables for the Responses (i.e. the student picked "A"), the identifier for that specific choice (i.e. "A" for Q1 is 45), and their value (i.e. choice "A" on Q1 gets 3/5 points). I haven't yet gotten the feel for knowing when you need to add separate tables or not.  

      While the identifier is not totally critical, I'd like to be able to see why their answer is wrong, not just whether or not it was wrong.

      Thanks in advance for any guidance anyone can provide,


        • 1. Re: Number of Tables for Test

          Seems like you'd need this relationship:

          StudentResponses::QID = AnswerValues::QID AND
          Answer = AnswerValues::Answer

          That's a single relationship that uses two fields in order to match up values so that you can access a specific value for a specific student response.

          • 2. Re: Number of Tables for Test

            I think I may have a problem with the calculation I created.  I would like my AnswerValue field to return "Correct" or "Incorrect" based on whether that answer it the correct one.

            If ( Student Answer = "5"; "Correct" ; "Incorrect")

            In this example the correct answer is choice 5.  What I am a bit confused on is that each QID is going to have a different correct answer.  How do I specify that that specific "Correct" only applied to questions with QIDs of "5"?

            I suppose I could make a very long Case calculation, where if QID = # and the Answer for that QID is the correct #, it will return "Correct", if not, "Incorrect". 

            • 3. Re: Number of Tables for Test

              I think your last post confuses a Question's ID number, QID, with the value of the correct answer for that question. Those are two different field and two different quantities.

              Your question is the point of my suggested relationship.

              If Your StudentResponse record has a QID of 235 and the correct answer is 5 and is stored in an AnswerValues record with QID 235 and Answer = 5, then you can test for correct or inccorrect responses with this calculation defined in StudentResponses:

              If ( AnswerValues::Answer = Student Answer ; "Correct" ; "Incorrect" )

              The QID value in StudentResponses matches it up to just one record with the same QID (235 in our example) in AnswerValues in order to look up the correct answer.

              • 4. Re: Number of Tables for Test

                When I used the calculation, "Incorrect" was returned for all cases.  I think this probably has something to do with my relationship.  That leads me to believe that I misunderstood your relationship suggestion:

                StudentResponses::QID = AnswerValues::QID AND
                Answer = AnswerValues::Answer

                I guess I don't quite get how to define a second part to a single relationship.  When I tried to define it, I only was able to end up making:

                StudentResponses::QID = AnswerValues::QID AND
                StudentResponese::Answer = AnswerValues::Answer

                ...which ends up being two relationships, not a single one matching two fields.  I was wondering if you could elaborate a bit. 

                Thanks again.

                • 5. Re: Number of Tables for Test

                  It's one relationship with two parts, and my earlier post was in error it should read exactly as what you have posted. I left out the table occurrence name in the start of the second line. Embarassed

                  That relationship should match up a student's response to a specific answer record. If you have QID = 125, you have at least one record in AnswerValues with QID = 125 and the correct answer stored in the Answer field.

                  IsEmpty ( AnswerValues::QID ) is a calculation that tells you that the current StudentResponses record is incorrect if true and is correct if false. That said, I'm changing directions on you a bit here. I think you would be better off just to define an answer field in the Questions table for recording the correct answer. This would then allow you to use a simpler relationship:

                  StudentResponses::QID = Questions::QID

                  To find and compare the student response to the correct answer: If ( StudentResponses::Answer = Questions::Answer ; "Correct" ; "Incorrect")

                  The only advantage I can see on this Monday morning to having a separate answers table is if you need to maintain a list of more than one correct answer for a given question--which isn't a typical test design strategy, but could be done.

                  • 6. Re: Number of Tables for Test

                    I was able use the calculation "If ( Answer Scores::Answer = Student Answers::Student Answer ; "Correct" ; "Incorrect" )" to get the some expected results (I have only checked a few examples).  However, this was done without the 2-part relationship you mentioned earlier.  Could this cause a problem, only using the StudentResponse::QID=AnswerValues::QID relationship?

                    Edit:  This was written before I was aware of PhilModJunk's response.  It is probably safe to ignore, as he already answered my question. 


                    • 7. Re: Number of Tables for Test

                      I'd like to make it display the answers my student chose (1-5) and what those answers actually represent (1=red, 2=blue), along with some other information.  However, I realize now that I don't have a solid way to relate the answer chosen with what that answer (1-5) correlates to their labels.

                      I thought it would work if I did this:

                      Student Answer Table: 

                      1. UID
                      2. QID
                      3. Answer Chosen (1,2,3,4,5)

                      Answer Label Table:

                      1. QID
                      2. Choice (1,2,3,4,5)
                      3. Label

                      When I would generate a report, I wanted it to display a list of the students, and within each section, all the questions they answered.  The "Answer Chosen" would then make the field next to it display that particular label.

                      Does anyone know a better way to relate these?  For some reason, I can't think of a good way to do so.  I've had trouble looking for other similar projects on the forums as well as other sites, but not many people seem to use (or post about it at least) for academic (testing) databasing.  So, it's been hard to compare my database design with others.

                      • 8. Re: Number of Tables for Test

                        The additional table works provided your relationship matches by both choice and QID as I am reading that post to mean that for a given Question, there would be 5 records, each with a different answer Chosen value.

                        The answer chosen by the student could actually enter the "label" instead of the number. Conditional value lists can be devised that list different choices for each question.

                        One way that I've done this is to Have these fields in my question table

                        If the list of choices for a question are 1 = Apple; 2 = Orange; 3 = Kiwi; 4 = Apricot ; 5 = Peach,

                        I put this list in the ChoicesList field:


                        I can then present this list as radio buttons so that when a student clicks the first radio button, Apple is entered into the student response record for that question. On a different question, you can present an entirely different list of choices.

                        • 9. Re: Number of Tables for Test

                          I'm seeing some odd activity when trying to create my label table.  Originally, the fields I had were:  QID, Choice (not Student Answer), and Answer Label.  I see now that I should have just made that field "Student Answer"  and relate that to my Student Answer Table.  The problem is that when I add the Student Answer Field to my Labe Layout/Table, it is inserting Student Answers into the current records.  I don't want to alter them, as they might change the actual answers in the other table.  Also, even if I update them, they just become a different answer anyway.

                          I tried deleting the records holding all the answer lables, and starting from scratch there.  But now I cannot fill in the "Student Answer" portion of the table.  I'm not quite sure what to do at this point. 

                          • 10. Re: Number of Tables for Test

                            Yes, even after deleting all the label records and making new ones, Filemaker keeps auto-updating them or something. 

                            For example:  I want the first record to signify QID =1, Student Answer = 1, Label = only one string

                            I'll create a record like that, but as soon as I create a new record, QID = 1, Student Answer = 2, Label = all three strings, the previous records Student Answer Field is changed to 2.  I don't want Filemaker to change any of my data, I'm not sure why it's doing this, but it probably has to do with it assuming I want data auto-filled from the Student Answer table.  This seems silly to me though, since the Label records should not relate to the Student Answer records in a 1:1 relationship.  By this I mean:  The Student Answer table has hundreds of records while the Label table only needs about 50 records (10 questions, each with 5 choices).

                            • 11. Re: Number of Tables for Test

                              "it probably has to do with it assuming I want data auto-filled from the Student Answer table."

                              That won't happen unless you have specifically set up the fields and relationships needed for that to happen. Instead, something else isn't set up correctly with your relationships. Best guess is that you have a field to this table on a layout for student responses so that when you enter a student response, you are instead entering the data directly into the related table of answers instead of the table of student responses.

                              • 12. Re: Number of Tables for Test

                                i currently have a summary field in my report ("Total Score") that calculates the total amount of points earned by each student.  I now would like to also display the "Average Total Score" as well as an average score for each question.  

                                I was under the impression that I could use a summary field in a calculation by using the GetSummary function.  However, I tried to use the average function to calculate the average total score:  Average ( GetSummary ( Total Score; UID ) )...but this returned an error.  Is this because I'm trying to use a function on a function? The error was something like, "In the function...an expression was found where a field alone is needed."

                                I tried to use a different technique to calculate the average total score (using another summary field), but you cannot create a summary field based on a summary field (I was going to take the Average of Total Score). 

                                Is there a better way to calculate the Average Total Score and the Average Score Per Question?

                                • 13. Re: Number of Tables for Test

                                  I'm thinking I can avoid going the summary field route if I create a calculation field that will calculate the average of all scores the the database with the same QID.  I'm not sure how to do that yet, so I'll look up some examples.  I'm sure someone has done something quite similar to this. 

                                  • 14. Re: Number of Tables for Test

                                    You have two methods available for aggregate values such as a total or an average. Summary fields are one method. Aggregate functions such as Sum(), Count(), Average() are another. Aggregate functions are intended to take a list of fields as their parameters which is why you go the error message you saw. The "list of fields" referenced by an aggregate function can take three forms:

                                    Sum ( field1 ; field2 ; field3...)
                                    Sum ( RepeatingField ) //each repetition is summed
                                    Sum ( RelatedTable::field ) //all instances of field from matching records in the related table.

                                    You may find your test average calculations easier to set up if you have a table where you have one record for each test taken by a student. Sum and average functions defined in that table can then compute totals or averages for a specific test taken by a student by referencing the related student response records for a given combination of studentID and QID.

                                    1 2 Previous Next