Aggregate function error

[LAS]

I get this error
"You tried to execute a query that does not include the specified expression 'ProbBehaveCat1_Desc' as part of an aggregate function."

when I execute query 2. Query 1 works fine. Before Access blew up on me, a function that produced Query 2 also worked fine. HELP!!! Can anyone figure out what might be wrong?

QUERY 1 - typed directly into the query builder
SELECT Sum(fncElapsedTime(Incident_Time,Incident_Return_Time)) AS Expr1, tblStudentTracking.ProbBehaveCat1_Desc
FROM tblStudentTracking, tblStudents
WHERE tblStudents.Student_ID=tblStudentTracking.Student_ID
And tblStudentTracking.Student_ID=374
And nz(tblStudentTracking.ProbBehaveCat1_Code,'')='xOther'
GROUP BY tblStudentTracking.ProbBehaveCat1_Desc
ORDER BY tblStudentTracking.ProbBehaveCat1_Desc;


QUERY 2 - Cut and pasted from Immediate View in a function that builds this query string (end of line inserted by me)
SELECT Sum(fncElapsedTime(Incident_Time,Incident_Return_Time)) AS Expr1, tblStudentTracking.ProbBehaveCat1_Desc
FROM tblStudentTracking, tblStudents
WHERE tblStudents.Student_ID=tblStudentTracking.Student_ID
and tblStudentTracking.Student_ID = 374
And nz(tblStudentTracking.ProbBehaveCat1_Code,'')='xOther'
GROUP BY tblStudentTracking.ProbBehaveCat1__Desc
ORDER BY tblStudentTracking.ProbBehaveCat1__Desc;

 

[John W. Vinson]

I get this error
"You tried to execute a query that does not include the specified expression 'ProbBehaveCat1_Desc' as part of an aggregate function."

when I execute query 2. Query 1 works fine. Before Access blew up on me, a function that produced Query 2 also worked fine. HELP!!! Can anyone figure out what might be wrong?

QUERY 1 - typed directly into the query builder
SELECT Sum(fncElapsedTime(Incident_Time,Incident_Return_Time)) AS Expr1, tblStudentTracking.ProbBehaveCat1_Desc
FROM tblStudentTracking, tblStudents
WHERE tblStudents.Student_ID=tblStudentTracking.Student_ID
And tblStudentTracking.Student_ID=374
And nz(tblStudentTracking.ProbBehaveCat1_Code,'')='xOther'
GROUP BY tblStudentTracking.ProbBehaveCat1_Desc
ORDER BY tblStudentTracking.ProbBehaveCat1_Desc;


QUERY 2 - Cut and pasted from Immediate View in a function that builds this query string (end of line inserted by me)
SELECT Sum(fncElapsedTime(Incident_Time,Incident_Return_Time)) AS Expr1, tblStudentTracking.ProbBehaveCat1_Desc
FROM tblStudentTracking, tblStudents
WHERE tblStudents.Student_ID=tblStudentTracking.Student_ID
and tblStudentTracking.Student_ID = 374
And nz(tblStudentTracking.ProbBehaveCat1_Code,'')='xOther'
GROUP BY tblStudentTracking.ProbBehaveCat1__Desc
ORDER BY tblStudentTracking.ProbBehaveCat1__Desc;

Are you intending Desc to mean "sort in descending order"? What in fact is the
field name in your table... ProbBehaveCat1 or something else?

If my guess is correct try

SELECT Sum(fncElapsedTime(Incident_Time,Incident_Return_Time)) AS Expr1,
tblStudentTracking.ProbBehaveCat1
FROM tblStudentTracking, tblStudents
WHERE tblStudents.Student_ID=tblStudentTracking.Student_ID
And tblStudentTracking.Student_ID=374
And nz(tblStudentTracking.ProbBehaveCat1_Code,'')='xOther'
GROUP BY tblStudentTracking.ProbBehaveCat1
ORDER BY tblStudentTracking.ProbBehaveCat1 Desc;
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[LAS]

Desc is part of the field name.

The field name, as used in both queries is ProbBehaveCat1_Desc

It works fine in one.

Not in the other.

Can you see a difference that would cause one to fail?

 

[John Spencer]

It would help if you posted the function that creates the string.

You said you inserted the line breaks in query 2. That changes the query and
hides any occurrence of missing spaces between elements of the query string.

If you paste the code generated string directly into a new query (in SQL
view), does it run or does it error? If you paste the code as modified (with
the line breaks) does it run or does it error?

Also, I wonder why your query is using the NZ function at all in this line.
And nz(tblStudentTracking.ProbBehaveCat1_Code,'')='xOther'
when the following will return the exact same records
And tblStudentTracking.ProbBehaveCat1_Code='xOther'

Also, why are you not using a join in the query. The following query should
be much more efficient.

SELECT Sum(fncElapsedTime(Incident_Time,Incident_Return_Time)) AS Expr1
, tblStudentTracking.ProbBehaveCat1_Desc
FROM tblStudentTracking INNER JOIN tblStudents
ON tblStudentTracking.Student_ID =tblStudents.Student_ID
WHERE tblStudentTracking.Student_ID=374
And tblStudentTracking.ProbBehaveCat1_Code ='xOther'
GROUP BY tblStudentTracking.ProbBehaveCat1_Desc
ORDER BY tblStudentTracking.ProbBehaveCat1_Desc;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

Also, I wonder why your query is using the NZ function at all in
this line.
And nz(tblStudentTracking.ProbBehaveCat1_Code,'')='xOther'
when the following will return the exact same records
And tblStudentTracking.ProbBehaveCat1_Code='xOther'

I had to think through this one.

If you do As John suggested, all the Nulls will be excluded.

If you leave it alone, rows with Nulls will be tested against a ZLS,
and they will be excluded because ""<>"xOther".

So, yes, John is right -- there is no utility in the Nz() test, just
a performance drain.

 

[David W. Fenton]

Also, why are you not using a join in the query. The following
query should be much more efficient.

SELECT Sum(fncElapsedTime(Incident_Time,Incident_Return_Time)) AS
Expr1 , tblStudentTracking.ProbBehaveCat1_Desc
FROM tblStudentTracking INNER JOIN tblStudents
ON tblStudentTracking.Student_ID =tblStudents.Student_ID
WHERE tblStudentTracking.Student_ID=374
And tblStudentTracking.ProbBehaveCat1_Code ='xOther'
GROUP BY tblStudentTracking.ProbBehaveCat1_Desc
ORDER BY tblStudentTracking.ProbBehaveCat1_Desc;

Not necessarily -- Jet/ACE generally optimizes an explicit JOIN
exactly the same as the equivalent implicit JOIN (i.e., a "join"
done in the WHERE clause). While I see no reason not to use the JOIN
(since it makes more sense), I don't think it will result in a
performance improvement (though I can't recall that I've ever run
SHOWPLAN on implicit joins in aggregate queries).