Aggregate function

[ibbledibble]

Hi

I've tried to create a simple query pulling together a simple count of three separate fields from two different tables. All fields are date fields, which have criteria for the choosing records from the previous month. However, when I run it I get an error message saying that the selection criteria are not written as aggregate function.

In SQL view, it looks like this:

SELECT Count(tblPPQs.PPQdate) AS CountOfPPQdate, tblPPQs.PPQType, Count(tblPPQs.PPQType) AS CountOfPPQType, Count(tblCaseTable.DateRec) AS CountOfDateRec, Count(tblCaseTable.ClosedDate) AS CountOfClosedDate
FROM tblPPQs, tblCaseTable
GROUP BY tblPPQs.PPQType, Year([PPQDate])*12+DatePart("m",[PPQDate])
HAVING (((Count(tblCaseTable.DateRec))=Year([DateRec])*12+DatePart("m",[DateRec])) AND ((Count(tblCaseTable.ClosedDate))=Year([ClosedDate])*12+DatePart("m",[ClosedDate])) AND ((Year([PPQDate])*12+DatePart("m",[PPQDate]))=Year(Date())*12+DatePart("m",Date())-1))
ORDER BY tblPPQs.PPQType;


The three fields I need to count are PPQDate, DateRec and ClosedDate.

Can anyone help?

Thanks!

 

[vbaInet]

Include all the other Year(...)*... code in the GROUP BY line. For example, add Year([DateRec])*12+DatePart("m",[DateRec])) to the Group By line and repeat the same for the others.