Avg With 0`s

[B.A.M]

Hi Again:

Im Trying To Get The Average Over A Group Without The 0`s In The Equation
To Remove The 0`s From The Equation

=Avg(Nz[Group],0)

Is It Somthing Like This???

I Have Hundeds Of Entrys With 0`s Among


Thanks....J

 

[speakers_86]

The Nz() function tests if the field is null, not zero. There is difference. Null means that the value simply does not exist. It does not equal anything. So you are saying to replace nulls with 0. Your actually increasing the number of zeroes!

I would write a function to figure this out. I don't know if this will suit your needs, but it should be close. I didn't test this at all, and the code to loop through a recordset came from here, though I'm sure you could do it more easily, but this should work fine, just input your sql.

Private Function GroupAverage as variant
    Dim r As DAO.Recordset
    Set r = CurrentDb.OpenRecordset("SELECT * FROM Contacts")

    Dim count as long
    Dim Sum as long

    'Check to see if the recordset actually contains rows
    If Not (r.EOF And r.BOF) Then
        r.MoveFirst
        Do Until r.EOF = True
            if nz(me.gourp) > 0 then count=count+1
            total=total+nz(me.group)
        'Move to the next record. Don't ever forget to do this.
        r.MoveNext
    Loop
   Else
       MsgBox "There are not records in the recordset."
    End If

    r.Close
    Set r = Nothing

    GroupAverage=count/total*100

End Function

 

[B.A.M]

Woo Way above Me
Im An Entry Level Access User

From What You Said I will Need To Change The 0`s To Empty Spaces (Null)

For Me To Get The Avg Of The Colume Of Numbers With Blank Entrys(0)`s
I Cant Use The Standard Formulas +,-,Sum Avg ect

Thanks.

 

[speakers_86]

Woo Way above Me
Im An Entry Level Access User

From What You Said I will Need To Change The 0`s To Empty Spaces (Null)

No I didn't.

For Me To Get The Avg Of The Colume Of Numbers With Blank Entrys(0)`s

Null and zero are not the same!

Check out the attachment, and see the image too to find out how to see the code.

 

[wilpeter]

Have you tried a simple query that has >0 as its criteria for that field and the other identifying group (if any) field? Save it, and use it as the source table for your AVG totalling query.

 

[B.A.M]

Sorry: This Is A Text Box At The Bottom Of A Existing Form
I Have The Totals (=Sum) Of The Data In The Colume But Need The Avg Also
If i Use (=Avg) It Calculates With The Default Entries Of 0 And I Get Scewed #

Thanks For The Understaning And Patience

 

[wilpeter]

I sympathize with your difficulty but although the original response from an expert would likely be the best approach, my suggestion was based on your inexperience. If you would clarify your situation, I might get you a solution within your comfort zone. You obviously have a table with many records, some of which include "0" as a default in a field that has been left without a number. Is the Form (to which you referred) an INPUT form to that same table? or is it the result of a query from the table, summarizing and displaying results? or is it perhaps a menu form (usually unbound) from which objects are launched by means of a macro/command button. How are data collected in this table--from multiple users, one user, several locations, Internet? Are you permitted to display any part of the database?

 

[B.A.M]

I Believe

It Is the result of a query from the table, summarizing and displaying results

Im Editing The Report In Design View And Changing Or Adding Text Boxes With Fomula`s

I Have Encosed 2 Pic`s

 

[wilpeter]

As you are by now aware, the report you illustrated will have been designed either from a query or directly from the table. (The report's Source property will reveal which it is.) The report need not change, but its Source can be changed to a query that has all the fields from that table plus a couple more.

Create a New Query (called "qryNonZero"). Add all the fields from the Table (you can use the * from the Table field list).
Then create a new field: NonZero: IIf([Cost]=0,0,1) and set the field's property Format to General Number.

Change the report's Source property to "qryNonZero".
Add the field, "NonZero" to the report as an extra column. You can make it visible or not with the Properties.

In the Report Footer, you can then add a calculated field: Average Cost: =Sum([Cost])/Sum([NonZero])
Does this need further explaining? Hope it helps.

 

[speakers_86]

I sympathize with your difficulty but although the original response from an expert...

Thank you!

would likely be the best approach,

There's more than one way to skin a cat (Isn't that a terrible expression?)! I showed myself how to do this with two different methods. 'Best' is subjective.

I actually wanted to help more, but I was at work all day, and I'm not quit in the state of mind to help right now.

 

[B.A.M]

Open The Report Click The Uper Most Left Bar (*)

(The report's Source property will reveal which it is.)


SELECT DISTINCTROW tblNetRepairs.Serial, tblNetRepairs.RepairDate AS [Date], tblNetRepairs.LastLocation, IIf(IsNull([Company])," ",[Company]) AS Compan, subRepairTypes.[Repair Type], tblNetRepairs.Repairer, tblNetRepairs.RepairDescription, [Net Inventory].Size, [Net Inventory].Mesh, [Net Inventory].Twine, [Net Inventory].DateMade, [Net Inventory].Active, tblNetRepairs.Invoice, tblNetRepair

I Believe That Is The Source Desciption
Is That Report Produced From 3 Different Tables

I Find The Information I Need Is In A Form But I Cannot Assosiate A Report With The Req Form (The Info Dose Not Seem To Be In The Tables)

Sooo...Painfull
OK I Can Create Another Report (Same Properties)
k Gimme A Sec To Digest

 

[wilpeter]

Forgive me, B.A.M., but since you are using SQL (and I'm not) your code bypasses what I would be doing with the GUI query approach. Someone familiar with SQL is needed. I gather this was not your designed database. Good luck.

 

[B.A.M]

No. Not Mine A Employee Far More Experianced In This
Hes An Anylist And Im From THe Field Therfore Im Trying To Massage The Database To Suite My/Our Needs

Ill Just Move On For Now There Are Alot Of Changes I would Like To Implement

Eg, The Form In THe Reports (Where You Pick The Criteria) Has To Many Valuse And It Covers All The Reports (Generic) i Would Prefer To See The Form With More Specific Valuse Assosiated With The Report To Be Designed

OOO The List Goes On
I Would Also Like A Back Button On The Form (Reports) As There Is No Way To Go Back A Page If I Want To Produce A Alternat Report (I Must Close And Restart The Database)

Thanks Again For Any Assistance That ANYONE Can Atribute

 

[B.A.M]

Ok Talked To The Professional And Was Saying Use Greater Than >

So You Know All My Editing Has Been On The Design View Of Reports

I Produce A Report Then Change views To Design View Then Edit The Text Box`s (Probably Not The Best Spot To Edit)

I Believe He Was Saying I Will Need To Do What Wilpeter Sugjested (Query)

I Will Try That Approch ..Thanks

 

[B.A.M]

(Simple Eg)
2
5
0
0
3
Total =2 Avg([Total])
I Want It To Be 3.33333

Im Told To Use The Factor ><(Less Or Greater Than)
To Figure The Problem Of The Equation Totaling The Average With Zero`s

I Have Done The "Create a New Query (called "qryNonZero"). Add all the fields from the Table (you can use the * from the Table field list).
Then create a new field: NonZero: IIf([Cost]=0,0,1) and set the field's property Format to General Number" AS Sugjested By WilPeter

Issue Is It Removes The Other Information Assosiated With The zero`s From The Report And That Info Needs To Be Present On The Report

If Only It Were Exell.. Then Id Be In A Different Forum...Lol

Thanks Again The Comunitey Here

 

[speakers_86]

Take a look.

 

[B.A.M]

Is This A 2010 Database It Has Missing Referances ETC

Thanks For The Help

 

[wilpeter]

Issue Is It Removes The Other Information Assosiated With The zero`s From The Report And That Info Needs To Be Present On The Report

Yes, the query as a stand alone object does what you want the formula Avg to do, (ignore the zeros). The resulting data can then be summed and counted or averaged. Admittedly, the average is being performed separately in a query but you can display the result in a subreport or whatever your "form" is.

Keep at it. I don't use Code, so also wasn't able to understand the previous post...but then I'm just a 2003 user anyway.

 

[wilpeter]

I've attached a sample database that might be useful.
Good luck.

 

[B.A.M]

Can It Be Something Like This =Avg([Hours]>400) And ([Hours]<1)

I Get An Ansewer Of Zero (0) For This Equation And Not ?Name!
Can I Be Close
I Had To Stop And Stretch Due To our Companys O.H.S.P


Thanks