Solved

Exclude zero values from avg

Posted on 2006-11-07
13
509 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

615 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