ERROR when trying to do Average in Query

[taimysho0]

Hello, i keep getting this message "you tried to execute query that doesnt include specific expression ""....

everything looks fine but i dont understand why it is not averaging. I want to average the number of days late, everything else

heres a copy of the sql, pic is also attached. thanks!




SELECT qryPiece1.CHANNEL_ROOT_ID, Format([problem_report_event_dttm],"mm/dd/yyyy") AS [Event Date], qryPiece1.PROBLEM_REPORT_EVENT_TXT, qryPiece1.CycleDay, qryPiece1.LastReadDate, qryDay4Report_Piece2.[Due Date], Date()-[Due Date] AS [Days Late], Avg([Days Late]) AS Expr1
FROM qryDay4Report_Piece2 INNER JOIN qryPiece1 ON (qryDay4Report_Piece2.Month = qryPiece1.Month) AND (qryDay4Report_Piece2.Serial = qryPiece1.CycleDay)
WHERE (((qryPiece1.PROBLEM_REPORT_EVENT_TXT) Not Like "*sxv0*" And (qryPiece1.PROBLEM_REPORT_EVENT_TXT) Not Like "*byt2*" And (qryPiece1.PROBLEM_REPORT_EVENT_TXT) Not Like "*sef4*" And (qryPiece1.PROBLEM_REPORT_EVENT_TXT) Not Like "*jdbj*" And (qryPiece1.PROBLEM_REPORT_EVENT_TXT) Not Like "*gasg*") AND ((qryPiece1.LastReadDate)<[Due Date]) AND ((Date()-[Due Date])>=1));

[aytee111]

You must use the Totals function - click on the Greek-looking "E" icon and see what happens.

[taimysho0]

i tried that and it says

cant have aggregate function in group by clause (avg(avg([days late])) if i change it to "avg"

and

cant have aggregate function in group by clause (avg([days late])) if i just leave it as group by

[aytee111]

Your "avg" cannot be in the field name line, it must be in the totals line.

[taimysho0]

the only thing is that it does not average the whole column, it just averages the row?

[aytee111]

More than the row - you are grouping by something, otherwise why would you need averages? Your picture is a little small, but it looks as though you would remove the last column and put the "avg" on the Days late calc column.