Crosstab won't run with unrecognized field (1 Viewer)

[terrytek]

I am trying to make a crosstab query using the following query as a basis:

SELECT qryTwoLastTestsAWValues.StudentID AS StudentID, qryTwoLastTestsAWValues.L.TestDate AS LastTestDate, qryTwoLastTestsAWValues.P.TestDate AS PrevTestDate, AcademicYr([LastTestDate]) AS AcademicYr, qryTwoLastTestsAWValues.SSChange, qryTwoLastTestsAWValues.GEChange, NRSGain([GEChange],[SSChange]) AS NRSGain
FROM qryTwoLastTestsAWValues
GROUP BY qryTwoLastTestsAWValues.StudentID, qryTwoLastTestsAWValues.L.TestDate, qryTwoLastTestsAWValues.P.TestDate, qryTwoLastTestsAWValues.SSChange, qryTwoLastTestsAWValues.GEChange;

Here is qryTwoLastTestsAWValues, which has two tables called L and P that are two copies of the same table that have to be compared:

SELECT L.StudentID, L.TestDate, tblTestType.TestType, L.Form, L.SSNum, L.GENum, qryNRSLevel.EducationalFuncLevel, P.TestDate, P.Form, P.SSNum, P.GENum, qryNRSLevel_1.EducationalFuncLevel, [L].[SSNum]-[P].[SSNum] AS SSChange, Round([L].[GENum]-[P].[GENum],2) AS GEChange
FROM qryNRSLevel AS qryNRSLevel_1 INNER JOIN (qryNRSLevel INNER JOIN ((qryTestingValComp AS L INNER JOIN qryTestingValComp AS P ON (L.StudentID = P.StudentID) AND (L.TestTypeID = P.TestTypeID)) INNER JOIN tblTestType ON L.TestTypeID = tblTestType.TestTypeID) ON qryNRSLevel.TestID = L.TestID) ON qryNRSLevel_1.TestID = P.TestID
WHERE (((L.TestDate) In (SELECT TOP 1 TestDate          FROM qryTestingValComp          WHERE StudentID=P.StudentID AND TestTypeID=P.TestTypeID          ORDER BY TestDate Desc) And (L.TestDate)<>[P].[TestDate]) AND ((P.TestDate) In (SELECT TOP 2 TestDate               FROM qryTestingValComp               WHERE StudentID=P.StudentID AND TestTypeID=P.TestTypeID ORDER BY TestDate Desc)))
ORDER BY L.StudentID, L.TestTypeID;

I am trying to do a crosstab query with the row heading Academic Yr, column NRS Gain, count of students as value. When I try to run it, I get a message saying the MS Database engine does not recognize "P.StudentID" as a valid expression. I tried aliasing StudentID as StudentID in the first query to no avail. I also tried joining the original student table to qryTwoLastTestsAWValues by StudentID and using tblStudents.StudentID but that didn't work either.

Is there something I can do to get the StudentID so it can be recognized and the crosstab query can run?

Thanks.

 

[June7]

So both SELECT queries work?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[pbaldy]

Note the bit on parameters:

http://allenbrowne.com/ser-67.html

 

[terrytek]

So both SELECT queries work?

If you want to provide db for analysis, follow instructions at bottom of my post.

Yes, both queries work.

 

[terrytek]

Note the bit on parameters:

http://allenbrowne.com/ser-67.html

Thank you! Declaring my column headings made the query run! Much appreciated!

 

[pbaldy]

Happy to help!