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.
Average of what exactly?
Sorry if I wasn't clear. 70 or higher would be the student's final grade. Just using the value that is already in plcae in the 'final_grade' field. I don't need the calculation to actually find the average for me.
Basically, there are two ways to do this:
1. Define another relationship between Students and Transcripts (using a new occurrence of Transcripts), filtered by course_name - so that only the two qualifying courses are related. Then you'll know if a student qualifies by testing for
Max ( Transcripts_xxx ::final_grade ) ≥ 70
2. (a little more tricky) Find where "Alg Day 1" is in List ( Transcript_09::course_name ) - then look for the value in the corresponding position in List ( Transcript_09::final_grade ). Do the same for the other course, and if either grade is ≥ 70, then... See here for more details:
Thanks. Is there a way to incorporate 'and ( Transcripts_xxx ::final_grade ) ≥ 70' somehow into my current calculation without setting up a new relationship with a new occurence of 'Transcripts' table?
Yes and no. That's what my point #2 above is about - but it's more of a "reconstruct" than "incorporate".
Got it now. cheers.