Calculating Avg based on date...

Discussion created by alinicjones on Oct 3, 2014
Latest reply on Oct 4, 2014 by erolst

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 Fields:

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