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
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.