3 Replies Latest reply on Aug 9, 2017 1:16 PM by philmodjunk

    What is the simplest way to create records in one table based on data in another?

    paulsar

      I am working on an education solution and I am trying to combine Q1, Q2, Q3 and Q4 data (all separate tables) into one main table. The primary issue I am having is figuring out how to add student records when they start after Q1 and not have a bunch of duplicate records.

       

      I have created a very inelegant solution that uses imports from one table to another, but there has to be a simpler way. I would love to get some advice on this. This table shows what I would like for the outcome to be -- all four quarter grades in one table. However, the data imports are provided for each quarter so, the data is imported into separate tables and look-ups occur to bring in the grade data.

       

      This table shows what I would like for the outcome to be -- all four quarter grades in one table. However, the data imports are provided for each quarter so, the data is imported into separate tables and look-ups (based on Student ID and Subject) occur to bring in the grade data.

       

      Last NameFirst NameStudent IDStatus 1SubjectQ1Q2
      Q3Q4
      OneStudent1YesMathB+BA-A-
      OneStudent1YesScienceA-AB+A
      TwoStudent2NoMathB-B
      TwoStudent2NoLanguage ArtsA-A
      ThreeStudent3NoMathB+B
      ThreeStudent3NoScienceB+B+

       

      In my database, the Q1 table is also the "main" table and has fields for all 4 quarter grades -- so, I only need to bring in the data from Q2-Q4. What is the most simple/elegant way to create new records in that table for the students (each unique combination of Student ID and subject) that arrive in Q2, Q3, and Q4?

       

      Thanks for any and all help on this problem.

        • 1. Re: What is the simplest way to create records in one table based on data in another?
          philmodjunk

          How you store the data and how you display it on the screen or printed page need not be the same.

           

          I'd use tables and relationships that look like this:

           

          Student---<SchoolYears-----<Quarters-----<Grades>-----Courses   (---< means "one to many")

           

          The fields in Grades would include:

          _fkReportCardID

          _fkCourseID

          Grade

          TeacherComments

          Other fields specific to this one grade for this one student for this one course.

           

          I'd then use one of several options for presenting this information such that the 4 grades for a given course are presented in the columns you show such that all for quarter grades are in one row for one course.

           

          To list a few options: a "horizontal portal" could list the individual grades in a row, so could a "virtual list" as could a repeating calculation field where the repetitions use ExecuteSQL to query data from Grades.

           

          You can research those terms as well as "pivot table" and "cross tab" to research these options and more for how you might do this.

          • 2. Re: What is the simplest way to create records in one table based on data in another?
            paulsar

            Thanks, Phil.

             

            I have been thinking about your suggested solution and I believe it would be a terrific way to generate a report card application. However, I am only processing data that already exists. My challenge is to get it from one format to another while also cleaning the variables for better analysis.

             

            To help you better understand the problem, I am attaching a sample spreadsheet that shows the type of data and how we receive it. Please note that I used four separate tabs, but the data would actually be delivered in four separate spreadsheets.

             

            Given this information, does that change your advice?

             

            Thanks!

             

            Paul

            • 3. Re: What is the simplest way to create records in one table based on data in another?
              philmodjunk

              If you are importing this data from a spreadsheet, it doesn't change my recommendation. You can set up a script that imports this data and moves it into the tables I've recommended. As long as the spreadsheet is kept to the same design of rows and columns, the user need not do more than select the file from which to import the data.

               

              The data model that I recommended makes for much more flexible options for how you might choose to work with this info.