student assesment db - If calculation with 2 field values?
I'm building a db of high school student assesment data. I have a table with basic student biographical/contact data and a related table called "transcript" of all courses each student has taken at our school along with the grades acheived. Each record in the trascript table has a field (course_name) as well as a field for final grade (final_grade).
Here's my issue...
We have multiple courses that satisfy our Algebra requiement. In the student bio table, I would like to create a "Yes/No" field that displays whether a student has successfully completed Algebra I with at least a 70 average or higher.
Currently I have this calculation for 2 courses that satisfy the Algebra I requirement........
If ( not IsEmpty ( FilterValues ( List ( Transcript_09::course_name ) ; "Alg Day 1 " ) ) or not IsEmpty ( FilterValues ( List ( Transcript_09::course_name ) ; "*Alg-gr8 " ) ) ; "Yes" ; "No" )
Now I would like to make the Yes/No dependent on an average of 70 or higher from the 'final_grade' field in transcript_09.
Any help on doing this and/or just making this calcuation simplier and more efficient?
Thanks again. I'm using version 8.5.