I have a database where i need to calculate the examiner's average only within 2 years. I have a table named Oral Exam and one name Facilitator.
Oral Exam Fields:
- OralExam ID
- FacilitatorID (match field)
- Oral Exam Date
- Exam Score
- Days Since Exam (Unstored, Calculation: Get (CurrentDate)-Oral Exam Date )
- Facilitator ID
- Overall Oral Exam Avg (Unstored, Calculation: ( Average ( facilitator_ORALEXAM::Score ) ) )
- Oral Exam Avg Criteria (Global) this is the amount of days that we want to see their examiner average from. Right now it is set for 730 days, which is 2 years.
- 2Yr Oral Exam Avg this is the field i need to figure out how to calculate.
I'm trying to do this though a relationship. But I must be doing something wrong. I made a copy of my Oral Exam Table Occurrence (TO) and connected it to my Facilitator TO. Matching the Facilitator ID and then connecting 2 fields I just created.
Facilitator Table – Include in 2Yr Avg (Text)(Global, Auto-enter Calculation) “Yes”
Oral Exam Table – Include in 2Yr Avg (Calculation) (Unstored, = If ( ( Days Since Exam <= oralexam_FACILITATOR::Oral Exam Avg Criteria ) ; “Yes” ; “” )
I matched those two fields to = each other.
I need to be able to use this field in a number of layouts so i can't just use a summary report option. I also need it to update itself. Any ideas and/or help you could provide would be greatly appreciated. Let me know if you need any further information. Thank you in advance.