Solved

Exclude zero values from avg

Posted on 2006-11-07
13
447 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now