Solved

Exclude zero values from avg

Posted on 2006-11-07
13
490 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

756 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