Solved

Exclude zero values from avg

Posted on 2006-11-07
13
480 Views
Last Modified: 2008-01-09
I have an access report column that I am doing an avg of

some of the values in the column are zero and I don't want to include them in the average

any ideas how to do this?

0
Comment
Question by:johnnyg123
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17892693
U would need to add that to the filter in your sql

or depending how how u use AVG, u could make use of domain aggregate functions in your query and add a filter in there
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17892697
select ....
from ...
where somefield <> 0

kinda thing
0
 

Author Comment

by:johnnyg123
ID: 17892730
that was my initial thought

however, for reasons unknown to me....

one of the folks in a different department wants to display the zero values in the column but not include them for purposes of avg


Tried to explain this is really a true avg but.....still wanted to know what the options are


0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 65

Expert Comment

by:rockiroads
ID: 17892805
so what if u used DAVG in your query?

0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 150 total points
ID: 17892817
e.,g

f1 is some field that identifies a record
f3 is the field's average u want


select f1, f2, f3, davg("f3","mytable","f1=" & a.f1 & " and f3 <> 0") as f3avg
from mytable as a



remember to use table aliase so u can join the right criteria
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17892828
johnnyq123 ....

That being said ....

>>>>>>>>>one of the folks in a different department wants to display the zero values in the column but not include them for purposes of avg
Tried to explain this is really a true avg but.....still wanted to know what the options are<<<<<<<<

1.) On your report (detail section) you could create two invisible text controls to sum the value of your field and count to get the aggregate based on the value not being zero.  Then use those in the report's footer (or group footer) to perform the average.

Field1, set the control source to =[YourFieldName] and set the Running Sum to OverAll or OverGroup.

Field2, set the control source to IIF([YourFieldName]=0, 0, 1) and set the Running Sum to OverAll or OverGroup.

In the Report's Footer or the Group Footer create a text control and set the control source to ...
=Field1/Field2

ET



0
 
LVL 44

Accepted Solution

by:
GRayL earned 200 total points
ID: 17893444
Change the zero values to null.  Nulls do not affect an average.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17895258
>>>Change the zero values to null.  Nulls do not affect an average.<<<<

Good catch GRayL ....  That being the case, he could do the Avg in a Query (grouped by his field) then join that Query to the main report's Query.  All he would have to do is display the Avg on the report since it will have been calculated in the underlying record source's query.

ET

0
 
LVL 44

Expert Comment

by:GRayL
ID: 17895505
If you have gone through some trouble to 'make sure' zero's appear in the report instead of blank, that is your problem. Remove the nz() function from the troublesome control.
0
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 150 total points
ID: 17895579
Yep ....   ITA GRayL.  The zero values in the underlyig table should be NULL.  Then just use the Avg Function in the report and you can format the report field IIF IsNull then show 0.

I have some clients like that where they have to see the 0's on the report when the field is empty.

ET
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17895662
It's what I call an incongruity.  Zero is a value and affects an average.  Null is nothing and doesn't.  The customer who has to see a zero but doesn't want it to affect the average is incongruous - or nuts;-)
0
 

Author Comment

by:johnnyg123
ID: 17898225
Appreciate all the posts!

I don't disagree one bit with GRayL comment that "The customer who has to see a zero but doesn't want it to affect the average is incongruous - or nuts;-)"

Believe me....I have expressed that to the customer in no uncertain terms

I will split the points between the 3 folks that were kind enough to respond.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17899624
Thanks, glad I could help.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question