#Error showing in a query

[mama16]

Hi, I have this query,

SELECT Deployers.Deployers, Deployers.Population, Sum(Statistics.Tot_calls_by_Month) AS System_Total, Deployers.Months_Operational, Max(Statistics.Tot_calls_by_Month) AS PMTot, Sum([Tot_calls_by_Month])/([Months_Operational]) AS AvgPM, Sum([Tot_calls_by_Month])/([Months_Operational])/([Population]) AS CPP, Sum(Nz([Statistics.Peak_call_count],0)) AS PCallCount, Sum(Nz([Statistics.Peak_call_hour_count],0)) AS PHourCount, Sum(Nz([Statistics.N_of_dropped_calls],0)) AS Dcalls, Sum(Nz([Statistics.Average_call_length],0)) AS AvgCLlength, Sum(Nz([Statistics.Total_minutes_per_month],0)) AS TotMPMonth, =Sum([Statistics.P_wireless])/Sum(IIf([Statistics.P_wireless]=0,0,1)) AS Wless, =Sum([Statistics.P_wireline])/Sum(IIf([Statistics.P_wireline]=0,0,1)) AS Wline, =Sum([Statistics.P_category])/Sum(IIf([Statistics.P_category]=0,0,1)) AS Cat, =Sum([Statistics.Traffic])/Sum(IIf([Statistics.Traffic]=0,0,1)) AS Traf

When I run it I get this,
I choose this column "P_Wireline"
as an example. I get the same thing for every field I used the ([])/Sum(IIf([]=0,0,1)) function


P_Wireline

0
0.476781483049746
0
#Error
#Error
#Error
0.447277781036165
0
#Error
#Error
0.600000023841858
#Error
#Error
0.436100006103516
#Error
#Error
#Error
#Error
#Error
#Error
3.99999991059303E-02
1.7092591971159

Why am I getting the #Error?

 

[GingerR]

looks like you trying to divide by 0?

 

[mama16]

I want to add up the values, then divide by them
for example

Month1 Month2 ..... .....
2 2
3 1
6 5
0 0
0 5

11/3 13/4

Would this function do it
Sum([Statistics.P_wireline])/Sum(IIf([Statistics.P_wireline]=0,0,1)) AS Wline ?

 

[mama16]

Please help

 

[lespaul]

is the data really set up like this:

Month1            Month2        .....       .....
2                   2
3                   1
6                   5
0                   0
0                   5

Are there 0's where the data is missing or are they really null?





Leslie

 

[mama16]

Yes, some are null and some has zeros.
Starts from June 2002 till present.

 

[lespaul]

So you have a table with a column for each month since June 2002?

Leslie

 

[mama16]

The field is MonthYear, it contains statistics for certain states
Jun-02
Jul-02
Aug-02
.
.
.
.
Jan-03
Feb-03
etc

Everything it's been working fine. If I run the report on a monthly basis, it gives me what I want.
I'm trying to run the report as a Total for the whole thing and it's not giving me the values I want

 

[lespaul]

What's the query that gives you the monthly totals? Is that the one above?

Leslie

 

[mama16]

This one here,,

SELECT S.Deployers, Sum(Deployers.Population) AS Population, Sum(Nz([S.Tot_calls_by_Month],0)) AS Total, Sum(Nz([S.Tot_calls_by_Month],0))/Sum(Nz([Deployers.Population],0)) AS Yield, Sum(Nz([S.Peak_call_count],0)) AS Peak_call_count, Sum(Nz([S.Peak_call_hour_count],0)) AS Peak_call_hour_count, Sum(Nz([N_of_dropped_calls],0)) AS Dropped_Calls, Sum(Nz([S.Average_call_length],0)) AS Average_call_length, Sum(Nz([S.Total_minutes_per_month],0)) AS Total_Minutes_Per_Month, 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, Sum(Nz([S.Services],0))+Sum(Nz([S.Services_garage],0))+Sum(Nz([S.Services_shopping],0))+Sum(Nz([S.Services_antiques],0))+Sum(Nz([S.Services_dining],0))+Sum(Nz([S.Services_lodging],0)) AS Services, Sum(Nz([S.Road_condition],0)) AS Road_condition, Sum(Nz([S.Transfer],0)) AS Transfer, Sum(Nz([S.Airports],0)) AS Airports, Sum(Nz([S.Bicycling],0)) AS Bicycling, Sum(Nz([S.Commuter_incentives],0)) AS Commuter_Incentives, Sum(Nz([S.Paratransit],0)) AS Paratransit, Sum(Nz([S.Carpooling_vanpooling],0)) AS Carpooling_Vanpooling, Sum(Nz([S.Spare_the_air],0)) AS Spare_the_air, Sum(Nz([S.No_selection],0)) AS No_selection, Sum(Nz([S.Travel_times],0)) AS Travel_times, S.Peak_call_day_reason
FROM Deployers INNER JOIN Statistics AS S ON Deployers.Deployers = S.Deployers
GROUP BY S.Deployers, S.Peak_call_day_reason, [Total_Minutes_Per_Month]/[Population], S.MonthYear
HAVING (((S.MonthYear)=[What month? Like Sep-2004]))
ORDER BY S.Deployers;

Then, at the bottom of the report I added text boxes for totals and did this for the % ones
=Sum([Commuter_Incentives])/Sum(IIf([Commuter_Incentives]=0,0,1))

Doing this I don't have to do it every month, count the values that are non zero and divide for how many there are

 

[mama16]

Is there anybody out there? Pink Floyd!!!

 

[lespaul]

And what are the results of this query?

what if you use this query as the basis of a new query where you sum all the individual months?

Leslie

 

[GingerR]

Hi mama16--While we enjoy helping, we are not all monitoring the forums 24/7. It's a bit tactless for you to be shouting at us. I'm not sure if that's the title of a song or not.

Your sql statement is a bit overwhelming to read through. It still seems to me that you are trying to divide by zero. Leslie is right: if the original query works fine, write a second query based on the original one that addes up the months.

Thanks.

 

[mama16]

Dear Ginger, I was not shouting at you guys. If you thought I was, please, accept my apologies!
And yes it is a title of a song by Pink Floyd.
You guys are great. I would never shout at you guys.

Mama