AnsweredAssumed Answered

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

Question asked by paulsar on Aug 7, 2017
Latest reply on Aug 9, 2017 by philmodjunk

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.

Outcomes