4 Replies Latest reply on Sep 29, 2013 11:27 AM by JulianSpark

    Newbie help with query/relationships

    JulianSpark

      Title

      Newbie help with query/relationships

      Post

           I am newbie, or at least it is about 15 years since I last worked with FileMaker Pro. I am looking for help with something I am trying to do.
            
           I want to use FileMaker to build a list of questions to be used in a test. The test comprises a number of questions organised into sections.
            
           I have a table of questions, with three fields -  questionID, sectionID, question_name.
            
           I have a sections table with two fields sectionID and section_name.
            
           These tables are related by sectionID
            
           I have a test table which has testID, test_name and sectionID1, sectionID2 etc
            
           SectionID1 is linked to sectionID of the sections table. SectionID2 is also linked to sectionID of the sections table.
            
           I thought I could use this set up to run a query on the questions table to produce an output which would have: test_name and all the questions which match the sectionID1, section ID2 etc criteria of the test table.
            
           But I can't get my head around it, I am missing something fundamental.

        • 1. Re: Newbie help with query/relationships
          philmodjunk

               What is the purpose of the Section table? And why would a test record have two different section ID fields?

               Can the same question be used on more than one test or be a member of more than one section?

          • 2. Re: Newbie help with query/relationships
            JulianSpark

                 Thanks for the reply.

                 If the Section table doesn't exist, then the information associated with sections needs to be part of the question record. I had thought that to keep the management of the question records simpler, it should contain just the section ID of the section to which that question belongs (the number of question records will become 000's). Also, although at present the questions have a one to one relationship with sections, it is likely that in the future, I will want to be able to have the questions belong to more than one section.

                 The test record has more than one sectionID as the test is compiled from questions belonging to different sections, for instance, test 1 might have 5 questions from section A and 3 questions from section B, test 2 might have 4 questions from each of section A, B and C.

                 The same question will be used in many different tests.

                  

            • 3. Re: Newbie help with query/relationships
              philmodjunk

                   OK, But what does a 'section' represent? What data common to all questions does that section record store? Just trying to get a complete understanding of how you need this to work. As someone who taught school for a few years, I can guess that a "section" either represents a section of your curriculum, a key concept or a state mandated standard. But that's just a guess.

                   

                        Also, although at present the questions have a one to one relationship with sections

                   That doesn't really make sense. From what you indicate in the reset of your response it would seem more likely that you have a one to many relationship; one section record matches to possibly many questions.

                   Your answers indicate that you actually will have at least two different many to many relationships. The relationship between sections and Questions is many to many: One section can link to many different questions and one question can be linked to more than one section. And then a test can link to many different sections and a section, I would guess, can link to many different tests.

                   Many to many relationships require the use of a join table. If I am right about the sections to questions relationship, a many to many relationship would look like this:

                   Sections-----<Section_Question>-----Questions

                   Sections::__pkSectionID = Section_Question::_fkSectionID
                   Questions::__pkQuestionID = Section_Question::_fkQuestionID

                   You can place a portal to Section_Question on the Sections layout to list and select a Questions record for each given Sections record. Fields from Questions can be included in the Portal to show additional info about each selected Questions record and the _fkQuestionID field can be set up with a value list for selecting Questions records by their ID field.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              • 4. Re: Newbie help with query/relationships
                JulianSpark

                     Thanks, I'll take a look and get back if I am still struggling.