Dcount in MSaccess

[kkronline]

Hello all

My SQL View of my Query in Access 2002.

SELECT Result.LotName, DCount("[Category]","Result","[Category] >21") AS Expr1
FROM LotProperty INNER JOIN Result ON LotProperty.LotName = Result.LotName
GROUP BY Result.LotName;

I need to count the category which is greater than 21 from the table Result and group by Lot name.

it is showing all same values.

can anybody help.

[MMcCarthy]

Try this instead ....

SELECT Result.LotName, Sum(IIf([Result].[Category]>21,1,0)) AS CountLots
FROM LotProperty INNER JOIN Result 
ON LotProperty.LotName = Result.LotName
GROUP BY Result.LotName;