Select Query Trouble

[ms_access_00]

I am having trouble with a select query I am creating. I am only trying to show the number of records with a certain "status" and that the "provider" field begins with KGF and the "Discipline" field isnt equal to PRB.

SQL Code:

SELECT [Overall PRB Tracker].Provider, [Overall PRB Tracker].Status, Count([Overall PRB Tracker].Status) AS CountOfStatus
FROM [Overall PRB Tracker]
GROUP BY [Overall PRB Tracker].Provider, [Overall PRB Tracker].Status
HAVING ((([Overall PRB Tracker].Provider) Like "KGF*" And [Overall PRB Tracker].[Discipline]<>"PRB"));

I get the an error saying: "You tried to execute a query that does not include the specified expression '[Overall PRB Tracker].Provider Like "KGF*" And Not [Overall PRB Tracker].[Discipline]="PRB" as part of an aggregate function.

Any ideas to help me with this?

 

[plog]

HAVING is essentially a WHERE clause on the aggregate values. There is no aggregate value for Discipline (it doesn't appear in the SELECT clause), so it is unable to find the field to apply its criteria to.

My suggestion is to change HAVING to WHERE and move it before the GROUP BY clause:

SELECT [Overall PRB Tracker].Provider, [Overall PRB Tracker].Status, Count([Overall PRB Tracker].Status) AS CountOfStatus
FROM [Overall PRB Tracker]
WHERE ((([Overall PRB Tracker].Provider) Like "KGF*" And [Overall PRB Tracker].[Discipline]<>"PRB"))
GROUP BY [Overall PRB Tracker].Provider, [Overall PRB Tracker].Status;