If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I make an average on a report out of formula totals?
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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201001/1 |
#2
|
|||
|
|||
How do I make an average on a report out of formula totals?
user via AccessMonster.com wrote:
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) -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
How do I make an average on a report out of formula totals?
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. -- Duane Hookom Microsoft Access MVP "user via AccessMonster.com" wrote: 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201001/1 . |
Thread Tools | |
Display Modes | |
|
|