Can't get the values I'm looking for.

[mama16]

This query is a little messy. I'm trying to calculate a total for the whole . Right now, it does calculations on a monthly basis.(I have to tell which month I want) I'd like to build up the query so it calculates a total for all the months. The db contains info from june-2002 till the Present
A field name called MonthYear( contains info like, jun-2002,jul-2002,Aug-2002 etc)

This is the query,
SELECT Deployers.Deployers, Deployers.Population, Sum(Nz([Statistics.P_wireless],0))/Sum(IIf([Statistics.P_wireless]=0,0,1)) AS Wless, Sum(Nz([Statistics.P_wireline],0))/Sum(IIf([Statistics.P_wireline]=0,0,1)) AS Wline, Sum(Nz([Statistics.P_category],0))/Sum(IIf([Statistics.P_category]=0,0,1)) AS Cat
FROM Deployers LEFT JOIN Statistics ON Deployers.Deployers = Statistics.Deployers
WHERE (((Statistics.MonthYear)=#6/1/2002#))
GROUP BY Deployers.Deployers, Deployers.Population, Deployers.Months_Operational;

The reason I'm using the Nz function is because some of those fields contain zeros and some are empties
An example of June-2002

Deployers P_wireless P_wireline P_category

Az 52.0 0 98.2
NC 0 53.6
SD 42.5 .06 3.0
FL 0 56.0

This query adds up the values then divides by the values that are non-zeros(P_wireless 52.0+42.5 /2
The month of July-2002 and on(Aug,Sep,Oct,Nov,Dec,Jan-2003,Feb etc.. have different values, I want the query to perform the same job. Then, At the end of October-2004, I'd like for the query to give me a total of everything.
Am I making any sense here?


Do I have to create a query from this query?
Any ideas,

Thank you

 

[lespaul]

what happens if you just remove the WHERE clause?

Leslie

 

[mama16]

I don't get the values than I'm supposed to get.

 

[lespaul]

if you want help, you are going to have to provide a little more information about what IS happening. Like:

Here's the original query:

[put query here]

Here are the results of this query:

[put results here]

I tried running this query:

[put modified query here]

and it gave me these results:

[put results of modified query here]

but what I want the results to return is this:

[put what you want the results to return here]

Now with this level of information, you are much more likely to get assistance that will actually help you and not frustrate those of us who are trying to provide assistance.



Leslie