Query

Quick question, I've got the below SQl statement which works well,
except I want the "CountOfOuts" to count all the values in the table
except those which have the value "NO" in it.

Is there a simple way?



SELECT DISTINCTROW MatchDetails.PlayerName,
Count(MatchDetails.Position) AS Innings, Count(MatchDetails.HowOut) AS
CountOfOuts, Sum(MatchDetails.Runs) AS Runs, [Runs]/Count([Position])
AS Average
FROM MatchDetails
GROUP BY MatchDetails.PlayerName;

What type of field is HowOut? Is it a text field? If so, you should be
able to use one of these expressions.

Abs(Sum(MatchDetails.HowOut "No")) --This will count howout as long
as it is not null or it is not No
or
Count(IIF(MatchDetails.HowOut="No",Null,1)) -- this will count HowOut
if it is null
Or
Count(IIF(MatchDetails.HowOut="No",Null,MatchDetai ls.HowOut)) --This
will count non-null values that are not "No"


"Paid The Umpire" wrote in message
ps.com...
Quick question, I've got the below SQl statement which works well,
except I want the "CountOfOuts" to count all the values in the table
except those which have the value "NO" in it.

Is there a simple way?



SELECT DISTINCTROW MatchDetails.PlayerName,
Count(MatchDetails.Position) AS Innings, Count(MatchDetails.HowOut) AS
CountOfOuts, Sum(MatchDetails.Runs) AS Runs, [Runs]/Count([Position])
AS Average
FROM MatchDetails
GROUP BY MatchDetails.PlayerName;