Averages problem

[KevinE via AccessMonster.com]

Hello,

I'm trying to do a simple average query as set out below:
SELECT [MAIN].Names, Avg([MAIN].Hrs) AS AvgOfHrs, Count([MAIN].Hrs) AS
CountOfHrs
FROM [MAIN]
GROUP BY [MAIN].Names, [Main].ID
ORDER BY [Main].ID;

My problem being that I would like to have all negative numbers register as
zero in the calculation.
Can this be done in a single query?
e.g.
5.0
1.5
2.5
-1.5
-1.0
3.0
Ave = 2.0

many thanks,
Kevin

 

[John W. Vinson]

Hello,

I'm trying to do a simple average query as set out below:
SELECT [MAIN].Names, Avg([MAIN].Hrs) AS AvgOfHrs, Count([MAIN].Hrs) AS
CountOfHrs
FROM [MAIN]
GROUP BY [MAIN].Names, [Main].ID
ORDER BY [Main].ID;

My problem being that I would like to have all negative numbers register as
zero in the calculation.
Can this be done in a single query?
e.g.
5.0
1.5
2.5
-1.5
-1.0
3.0
Ave = 2.0

many thanks,
Kevin

If you want to ignore negative records completely, both in the average and the
count, just include the Hrs field a second time to the query; use the WHERE
operator, and a criterion of >0.

If you want to count all times (even negatives) use

SELECT [MAIN].Names, Avg(IIF([MAIN].Hrs>0, [MAIN].Hrs, Null)) AS AvgOfHrs,
Count([MAIN].Hrs) AS CountOfHrs
FROM [MAIN]
GROUP BY [MAIN].Names, [Main].ID
ORDER BY [Main].ID;

 

[John Spencer]

SELECT [MAIN].Names
, Avg(IIF([MAIN].Hrs<0,0,[Main].hrs)) AS AvgOfHrs
, Count([MAIN].Hrs) AS CountOfHrs
FROM [MAIN]
GROUP BY [MAIN].Names, [Main].ID
ORDER BY [Main].ID;

That will treat negative numbers as zero, ignore any null values, and treat
zero and positive numbers correctly. That should give you the results your
sample does. That is a SUM of 12 divided by 6 non-null entries = 2

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County