Friday, March 9, 2012

Faiied Query

I am running SQL Server 2005 Express on the backend and Access 2000 on the front end.
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