A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old January 7th, 2010, 09:31 PM posted to microsoft.public.access.reports
User via AccessMonster.com
external usenet poster
 
Posts: 28
Default 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  
Old January 7th, 2010, 10:00 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old January 7th, 2010, 10:22 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:27 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.