I am trying to make a crosstab query using the following query as a basis:
Here is qryTwoLastTestsAWValues, which has two tables called L and P that are two copies of the same table that have to be compared:
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.
Code:
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:
Code:
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.