This query fails and it is not clear why?
SELECT DISTINCTROW tblTutoringHours.[Site ID] AS Expr1, tblTutoringHours.[Student ID] AS Expr2, Sum(tblTutoringHours.Hours) AS [Tutoring Hours], Min(tblTutoringHours.Level) AS [Start Level], Max(tblTutoringHours.Level) AS [End Level], [End Level]-[Start Level] AS Gain, Max([Date Graduated] Is Not Null)*(-1) AS GainGrad, Max([Drop ID]=8)*(-1) AS GainPromoted
FROM tblSites, tblStudents, tblTutoringHours
WHERE ((([tblSites].[Database Totals])=Yes))
GROUP BY tblTutoringHours.[Site ID], tblTutoringHours.[Student ID]
HAVING (((Sum([tblTutoringHours].[Hours]))>0))
WITH OWNERACCESS OPTION;
Doesn't look like you have all of the non-aggregate fields in the GROUP BY statement.|||
This calculated column contains columns that are not aggregrated and NOT in the GROUP BY:
[End Level]-[Start Level] AS Gain
This just won't work as written:
Max([Date Graduated] Is Not Null)*(-1) AS GainGrad
It could be revised as:
max( isnull( [Date Graduated], 0 ) * (-1) AS GainGrad
No comments:
Post a Comment