5 Replies Latest reply on Feb 18, 2010 3:57 PM by philmodjunk

    How to Combine multiple records into 1???

    mkiv

      Title

      How to Combine multiple records into 1???

      Post

       Hi all.  I'm trying to figure out a solution to the following task.  I've got a table with fields similar to the following:

       

      Student

      Class

      Grade

       

      Right now to data that's imported is in the following format (6 records).

      A - Math -3

      A - English -4

      A - Science -3

      B - Math -2

      B - English -3

      B - Science -3

       

      What I'm trying to accomplish is to get the data into the following format (2 records):

      Student      Math   English    Science

      A               3           4           3

      B               2           3           3

       

       I've thought about using Cross-tabs but would appreciate other sugguestions/solutions.

       

       

      Thanks in Advance

       

       

       

       

       

       

      In order for other forum participants to better help you, please provide the following information when posting:

      1. The version of FileMaker Pro you are using: FMP10
      2. The operating system and version you are using: Windows XP

      3. Describe as much detail about your database as is pertinent to your question.  Otherwise, other forum participants will have to guess and/or ask for more information.
      5. Try to indicate your level of experience with FileMaker and/or other database designs.  If the person responding to your post knows that you are a newer user, they can give a more detailed step by step description:Moderate


        • 1. Re: How to Combine multiple records into 1???
          davidanders
            

          A student is unique and has a StudentIDNumber.

          A student attends multiple semesters or quarters in multiple years related to student by StudentIDNumber in SemesterTable. 

          A student takes multiple classes in the semesters or quarters related to student by StudentIDNumber in ClassTable..

          Do you want to find by student?

          Do you want to find by semester?

          Do you want to find by class in a semester?

          There are three tables here so far. 

          • 2. Re: How to Combine multiple records into 1???
            mkiv
              

            The format for the data being imported does not have student as a unique ID.  It comes in similar to:

             

            A - Math -3

            A - English -4

            A - Science -3

            B - Math -2

            B - English -3

            B - Science -3

             

            where student A has 3 records and student B has 3 records (student C may have only 2).  What I'd like to do is consolidate the x records per student into one with the following format.

             

            Student      Math   English    Science

            A               3           4           3

            B               2           3           3

             

             

            In terms of performing finds it would be by student.

             

             

             

            Thanks in Advance

            • 3. Re: How to Combine multiple records into 1???
              philmodjunk
                

              What happens if you have two students named "john smith"? Do you have any procedure in place where this imported data is generated to enforce unique student names?  (by adding a middle initial or number to the second student with the same name for example.) If not, you could experience trouble down the road.

               

              Personally, I'd keep the entries in separate records but replace student names with unique student ID numbers if at all possible. There are several ways to display a "cross tab" style report that still leaves your records uncombined.

              • 4. Re: How to Combine multiple records into 1???
                mkiv
                  

                Hi Phil.  The example I gave above is a representation of the data I'm working with.  So it's not really to keep track of grades. =) I thought it'd be easier to explain using the grades example with only 3 fields.  Do you have any good references that speak more about cross tabs.  The only examples I've been able to come across use date fields in the calculations which in my case would probably be using the class field?

                 

                 

                 

                 

                Thanks in Advance

                • 5. Re: How to Combine multiple records into 1???
                  philmodjunk
                    

                  When you post abstract examples, you risk getting responses that don't apply due to the differences between your example and the real problem.

                   

                  Cross tab style reports generally work either from multiple relationships, one for each column or a series of portals set to display only row--with a different row of the related records specified in each column. Exactly how you do that depends on the specific data and what rules determine which records should report data in a given column.

                   

                  With either approach, you'll need a second table where you have one record for each "student" and use it for your report.

                   

                  Multiple relationships is probably the better approach here as you've indicated that not all "students" will have the same number of "subjects":

                   

                  Define a key field with an auto-entered value or a calculation that matches to a given "subject". For your "math" column, you'd define a key field in the report table that has the value "Math" and so forth for each column.

                   

                  Define a Table occurrence of your imported data's table for each column.

                   

                  Your relationships would look like this:

                  ReportTable::Student = col1TO::Student AND

                  ReportTable::MathKey = Col1TO::Subject

                   

                  Each column uses a similar relationship but with a different "subjectkey"

                   

                  Now place the place the "grade" field from col1TO in column 1, the "grade" field from col2TO in Column 2 and so forth for each subject.