How do I make an average on a report out of formula totals?

[user via AccessMonster.com]

I've made a report titled HR Efficiency Report. This report contains the
employee name, their pay rate and all of the information needed to calculate
formulas. I have successfully created a text box titled Total Eff that
properly displays the formula =[On Std Earned]/[Total Hourly Pay]. This
report is based on a query and I would like the user to be able to run the
query based on a single employee (which I have successfully done as well).
However, I am running into a problem when I try to get an average for my
Total Eff text box. Every time I try to open it it opens an Enter Parameter
Value box and asks me for the On Std Earned and the Total Hourly Pay. I have
both of these text boxes (which also contain formulas) in the report as well.
Why am I getting this message? Is there an easier way to get an average of
numbers that have been calculated already?

 

[Marshall Barton]

I've made a report titled HR Efficiency Report. This report contains the
employee name, their pay rate and all of the information needed to calculate
formulas. I have successfully created a text box titled Total Eff that
properly displays the formula =[On Std Earned]/[Total Hourly Pay]. This
report is based on a query and I would like the user to be able to run the
query based on a single employee (which I have successfully done as well).
However, I am running into a problem when I try to get an average for my
Total Eff text box. Every time I try to open it it opens an Enter Parameter
Value box and asks me for the On Std Earned and the Total Hourly Pay. I have
both of these text boxes (which also contain formulas) in the report as well.
Why am I getting this message? Is there an easier way to get an average of
numbers that have been calculated already?

You are getting the prompts because the aggregate functions
(Count, Sum, Avg, etc) do not know about controls (text box,
label, etc) in the report. They only know about fields in
the report's record source table/query.

That means you can get the desired answer by either moving
the calculations from report text boxes to calculated fields
in the record source query OR by replacing the text box
names in the Avg expression with the expressions in the [On
Std Earned] and [Total Hourly Pay] text boxes.

As an example of the latter approach, suppose you want to
average the total value of the items in an invoice and the
value of the items is calculated in a detail text box with
the expression =Qty * Price. Since you can not calcultae
the average using =Avg([detail total]), you would ned to use
=Avg(Qty * Price)

 

[Duane Hookom]

You can't aggregate (Sum, Avg, Min, Max,...) controls (text boxes) in
reports. You can only aggregate fields or expressions from the report's
record source.

You may need to add something to your report's record source so it becomes
available in your report. Check out my reply to "Avereging the subtotals in
Reports" thread a little more recent in this news group.