3 Replies Latest reply on Mar 30, 2009 9:35 AM by philmodjunk

    Merging Records

    Sendnot

      Title

      Merging Records

      Post

      I'm constructing a database using .csv data from an outside source. The goal of this database is to average test scores of students, then flag those who are in danger of failing their course(s). When I import the .csv file, I end up with 8 records per student, like this:

       

      Grade Student# LName FName Score Term Course

      06 00001 Learner Larry 85 1 English

      06 00001 Learner Larry 83 2 English  

      06 00001 Learner Larry 76 1 Math  

      06 00001 Learner Larry 78 2 Math   

      06 00001 Learner Larry 88 1 Social Studies

      06 00001 Learner Larry 84 2 Social Studies 

       

      etc...

       

      What I'd like to do is to have one record per student and then the scores in a field like EnglishTerm1, EnglishTerm2, MathTerm1, MathTerm2, etc... but if someone has a better method of achieving my goal of being able to print out a list of students whose average scores fall below 70 in any of the test categories, I'd be very appreciative!

       

      Also, the average must take into account that if there are scores with non-numeric or blank values, then those values won't affect the average.

       

      Thank you-   

        • 1. Re: Merging Records
          philmodjunk
            

           

          Grade Student# LName FName Score Term Course

          06 00001 Learner Larry 85 1 English

          06 00001 Learner Larry 83 2 English  

          06 00001 Learner Larry 76 1 Math  

          06 00001 Learner Larry 78 2 Math   

          06 00001 Learner Larry 88 1 Social Studies

          06 00001 Learner Larry 84 2 Social Studies 

           


          You need two tables. Use Manage Database to create:

          Table 1, StudentInfo, will have fields: StudentNumb, Grade, LastName FirstName.

          Table 2, Grades, will have Course, Score, Term and StudentNumb

           

          You will need to create a relationship linking StudentInfo::StudentNumb ----=----Grades::StudentNumb

           

          The real trick is in getting this information into the write table and eliminating duplicates from your StudentInfo Table.

          Open Field definitions for StudentInfo.

          Double-Click the StudentNumb field to bring up the Options Dialog Box.

          Select the Validation Tab and choose Require: Unique Value, Validate Data on this Field:  Always.

           

          Click OK twice to leave Manage Database.

          Select File|Import Records

          Select your .csv file as the source.

          Select StudentInfo as your target table.

          In the field matching part of the dialog, drag fields until the right fields align with the right columns in your .csv file.

          Import the records--You'll get a message reporting that not all records were imported. That's OK, the system just filtered out the duplicates you don't need.

           

          Now select File|Import Records.

          This time, select Grades as your target field and match up your Grades fields with the necessary columns.

          Import these records.

           

          Now enter layout mode and create a new layout. Select Grades as the table for this layout.

          Choose a list style report.

          Place the StudentInfo fields you want in the body of this report.

          Place the Grades fields you want in the same body of this report.

           

          Return to browse mode.

          Enter find mode.

          To find student scores that are 65 or lower, enter "< 65" (without the quotes) into the score field.

          Perform the find.

           

          That should get you started. Once that report works for you, you can try adding subsummary report parts to improve the report's look if you want.

          • 2. Re: Merging Records
            Sendnot
               I took your suggestion and I'm a bit closer to the goal, but how I can find averages for specific courses (like Q1 English, Q2 English and Q3 English)? Performing a find using "<65" as a criteria works as you said, but would have to be done on a field that averages the quarterly subject scores in order to complete this application.
            • 3. Re: Merging Records
              philmodjunk
                

              Check out Summary fields. One option for a Summary field is "Average of..." If you put that summary field in a sub-summary report part, you should see what you need.

               

              Note: I'm deliberately steering you away from have a field for each course in a combined record. As a former teacher, I know that course names and descriptions are frequently subject to change. It will be much easier for you to manage these changes with the table structure that I am recommending to you.