Average the contents of a field based on another field (1 Viewer)

[andrewaldrich]

I have a report that has multiple fields - 3 of which matter for this discussion -

1.) employee
2.) employer
3.) rateofpay

The employer field contains 1 of 2 options. For the sake of this conversation, lets call it ABC123 and XYZ123

I need to be able to calculate the average rate of pay for ABC123 company, and exclude the rate of pay for XYZ123 company in my report.

I am calculating this in a section footer.

Thanks for your help!

Andy

 

[ypma]

Would something like this do in a field in the report footer
=DAvg("[RateOfPay]","rateofpaytbl","[employer] ='ABC123'")

regards

 

[andrewaldrich]

ypma -

Thanks for your response. I tried this previously - unfortunately it averages over all records. I should have indicated that I need to average over subsets of records that are grouped by invoice number in my report.

Anyone else can weigh in?

I ended up doing a work around by creating an unbound field with an iif statement to fill in the unbound field based on the company name / employee rate of pay that I wanted to average. I think chose the field to not be visible, and shrunk it down and basically hid it on my report. Works exactly as I needed it to, but seems to be a work around to something that I should know how to do early.