4 Replies Latest reply on Jan 31, 2011 12:33 PM by philmodjunk

    Very Basic Lookup Help



      Very Basic Lookup Help


      Hi - 

      I have a database that has one table of students and another table of students' standardized test scores listed by the test number and then the test code with the test score (i.e. a single student will have 3 - 5 test instances). I want to create a layout that has the single student and a list of all of their test scores with a label indicating the test code or the type of test they received the score on.

      I don't know how to define the field in the new layout such that there will be a record for each student with a "lookup" of all of the students relevant test scores. I know this is probably very basic relational database stuff but I'm starting from scratch.

      I think part of the key here is that I need to match data within fields (the type of test) to the test score and then match all of those things to the same student who took all the different tests.

        • 1. Re: Very Basic Lookup Help

          Is your test score set up like this?


          And this relationship?

          Students::StudentID = TestScores::StudentID

          It's important that there be one and only one test score in a given TestScores record. (But there can be multiple TestScores records for a given student.)

          If that's your setup, I'd suggest a summary report based on the Test Scores layout, with fields from Students added as need to supply information about a given student in the report. You can use summary fields and Sub Summary layout parts with sorting to group your scores first by student and then by type of test score.

          • 2. Re: Very Basic Lookup Help

            Thank you, PhilModJunk!

            I think that's just the thing that I need to be in the test score table to make those sub summaries and grand summary. I was trying to create a field in the student table that would lookup the test scores and then I couldn't figure out how to create fields that would do the needed calculations (find averages/high/low/median of all our students in our program on the different tests, etc). (Also, those relationships look like the ones I have set up.)

            I think this means, however, that I have my test scores table formatted with incorrect fields. Within the test scores table (that lists the hundreds of different tests our students have taken individually) there is a field called "test code" and then there is a field called "test score." To manipulate this data better, would you recommend that I merge the two pieces of information so instead of "test code = Praxis I Mathematics"; "test score = 180" that I just have several fields that describe the test and then the data inside is the score? So field name  = "Praxis I Mathematics" ; data = 180, 182, 140. Field name "Praxis II: Social Studies Content"' data 172, 155, 190.

            In that case, for each test record I would have several field that have no data because each record only describes one test score.

            I'm running myself in circles. I'm not a DBA (obviously) just a teacher trying to organize!

            • 3. Re: Very Basic Lookup Help

              I think I just partly answered my own question by writing it out. Having all those test fields would be silly. I just need to write a script that would generate a report that would first sort by test score code and then do the calculations that I would need as part of the script. 

              The "sort" would take care of the fact that they aren't all the same test code.

              • 4. Re: Very Basic Lookup Help

                You've got it! (I think)

                If you want to explore summary reports further, here's a tutorial some have found useful: