Totals Query with 0 amounts

[jderrig]

I am working on a Totals Query. The query has a lot of calculations in it. I have one expression for Salary as a % of Gross Revenue. My issue is that I don't have salary information for each row (account). I only want to calculate the totals of those accounts with salary information without eliminating those accounts that do not have salary information.

I have 162 accounts and I need all 162 accounts to be totaled except in the column of Salary as a % of Gross Revenue where salary is 0 - this needs to be the exception.

 

[ypma]

If i understand the first part of your question, the normal way to sum with nulls is to use the nz functiion :
total: Sum(nz([wages])+nz([revenue]))

If I have missed the point, please provide a few examples with your desired outcome.

 

[jderrig]

What I am actually looking for is how to average the numbers as I can in this Excel formula that simply skips over the zeros and doesn't use those in the calculations.

= AVERAGEIF( M2:M7, "<>0" )

 

[JohnLee]

Hi,

If I'm reading your post correctly you've answered your own question, in the criteria row of your query beneath the field that contains that information enter Not Like "0". any values that are equal to zero 0 will not be included.

Regards

John

 

[ypma]

Have another idea will post next

 

[ypma]

John , i have made it work, using your method in that I used Not Like 0 in the criteria of the wages field of the query and then created the Expression :Averages:Avg([Wages]) which counts the records omitting the zero records .

JDerring , ignore my previous post, and hope this will give you something to work one.
Note the query is not a group by .
Bob

 

[jderrig]

Here is my issue in greater detail. Sorry for not being more specific...I've tried Dsum, Davg, Dlookup and nothing works correctly.

I have a totals query that is I have a DSum field where I want to sum only the field in these column where the salary expense is greater than 0: GrossRev. Since I don't have PersonnelExp for every account listing, it's skewing the true personnel expense as a percent of gross revenue. (I have other fields that I need to sum all the values in the column regardless of zero values)

Totals Query:
GrossRev: DSum("[GrossRevenue]","**Single account ","[Salary/BenefitExp] > 0")


The Totals Query is pulling from another query where I have filtered based on two parameters from a form where I can select the data between two years to give me the results.

**Single Account query:
Between [forms]![frmSingleAcctGraph]![Year 1] And [forms]![frmSingleAcctGraph]![Year2]

The DSum field is not producing the correct calculations.

Attached is a sample db.

When you open frmSingleAcctGraph and select just one year, you get the right GrossRev amount - 94985997 (which is the last column). It's when I select both years 2007 and 2008 from form and the last GrossRev column simply sums the the two years together and repeats the same total for each - 148930091.

The **Graph Output Single account query isn't recognizing the filter from on the **Single account query which is Between [forms]![frmSingleAcctGraph]![Year 1] And [forms]![frmSingleAcctGraph]![Year2]