Aggregate Functions in Where Clause

[Deleted member 28156]

I was wondering if anyone can help me: I have for example Pupils that are being entered into a table (called log). A query then counts the number of entries for each pupil I then want it ONLY to show the pupils that have been entered in 10 times on todays date (using DATE()).
I know this doesnt work but this is what I want to do:

SELECT log.pupilNo, log.Forename, log.Surname, Count(log.pupilNo) AS Demerits
FROM log, Tally
WHERE (log.Date)=Date() AND COUNT(log.pupilNo)>9
GROUP BY log.pupilNo, log.Forename, log.Surname, log.pupilNo;

Apparenty you cannot have an Aggregate function in a where clause.
Does anyone know a solution, any help would be appreciated
Cheers
Bikeboardsurf

 

[Len Boorman]

You need to do the aggregate as part of the Select clause. ther Where then becomes XXX=10 sort of thing

L

 

[Pat Hartman]

You need to use a having clause if you want to use aggregates as criteria.

A tip: when you don't know the syntax, just let the QBE build it for you.