Query calculation

[mama16]

I have to create a report on this query. There are some fields that have zero values and some don't. I want Access when creating the report and displaying the results to not show the values that are zero but show the ones that have numbers and all the Deployers.

An example,

A 23565
B
C 23658
D 26253
E 45698
F
G 124456

I don't want to see this,

A 23565
B 0
C 23658
D 26253
E 45698
F 0
G 124456

How can I tell Access to do that? Also, I tried to create a query that will sum up all the values that are not null and not zeros and divide by that x numbers, I couldn't do it. I tried to combine the NZ and the IFF function together to do the job but I still could not do it.
Any ideas? I'd appreciate.
I could add a text box on the report session and count how many have values and divide by those but I don't want to do that cause the parameter box is asking for the specific month and each month is going to have different values

This is the query
SELECT S.Deployers, Sum(Deployers.Population) AS Population, Sum(S.Tot_calls_by_Month) AS Total, Sum(S.Peak_call_count) AS Peak_call_Count, Sum(Nz([S.Peak_call_count],0)) AS SumOfPeak_call_countt, Sum(Nz([S.Average_call_length],0)) AS Average_call_length, Sum(Nz([S.Total_minutes_per_month],0)) AS Total_Minutes_Per_Month, [Total_Minutes_Per_Month]/[Population] AS Yield, Sum(Nz([S.P_wireless],0)) AS Wireless, Sum(Nz([S.P_wireline],0)) AS Wireline, Sum(Nz([S.P_category],0)) AS Category,
Sum(Nz([S.Traffic],0)) AS Traffic, Sum(Nz([S.Transit],0)) AS Transit, Sum(Nz([S.Weather],0)) AS Weather, Sum(Nz([S.Construction],0)) AS Construction, Sum(Nz([S.Ferry],0)) AS Ferry
FROM Deployers INNER JOIN Statistics AS S ON Deployers.Deployers = S.Deployers
GROUP BY S.Deployers, [Total_Minutes_Per_Month]/[Population], S.MonthYear
HAVING (((S.MonthYear)=[What month]))
ORDER BY S.Deployers;

 

[PHV]

Why not simply using a format ?
As you want all the Deployers, it's not a SQL problem but a report presentation one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mama16]

You mean on the report session?

 

[Blorf]

PHV I think means using the report to specifically not show zeros. Do a help search in a report for conditional formatting. Maybe make the font color white when the value is zero.

ChaZ

 

[OhioSteve]

Create a second query, and make it the report's data source. The new query's data source will be the fields from the first query. In the new query, set a parameter to exclude zeros.