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