Crosstab query error

[ridders]

Hi

I am trying to use the following crosstab SQL to run a report:

"TRANSFORM Last(LeaversGCSEResults.Grade) AS LastOfGrade SELECT
LeaversGCSEResults.PupilID, LeaversPupilData.Surname,
LeaversPupilData.Forename, LeaversPupilData.YearGroup,
LeaversPupilData.TutorGroup, LeaversPupilData.Gender,
LeaversGCSEResults.SubjectID, LeaversGCSEResults.Grade FROM
LeaversGCSEResults INNER JOIN LeaversPupilData ON LeaversGCSEResults.PupilID
= LeaversPupilData.PupilID In ('Ad', 'Af', 'Am', 'Bs', 'Ch', 'Ct', 'Da',
'Dr', 'Ds', 'El', 'En', 'Fr', 'Ge', 'Hi', 'Hs', 'Ht', 'It', 'Iv', 'Ln', 'Ma',
'Ms', 'Mu', 'PeG', 'Ps', 'Rs', 'RsS', 'Sm', 'Sp', 'Ss', 'St', 'Te', 'Tf',
'Tx') PIVOT LeaversGCSEResults.SubjectID;"

I get a syntax error (missing operator) in section after ON.
Please can anyone see what is missing where?

I have several similar reports based on crosstabs which work just fine

 

[Douglas J. Steele]

You're missing a Where clause:

"TRANSFORM Last(LeaversGCSEResults.Grade) AS LastOfGrade SELECT
LeaversGCSEResults.PupilID, LeaversPupilData.Surname,
LeaversPupilData.Forename, LeaversPupilData.YearGroup,
LeaversPupilData.TutorGroup, LeaversPupilData.Gender,
LeaversGCSEResults.SubjectID, LeaversGCSEResults.Grade FROM
LeaversGCSEResults INNER JOIN LeaversPupilData ON LeaversGCSEResults.PupilID
= LeaversPupilData.PupilID WHERE LeaversPupilData.PupilID In ('Ad', 'Af',
'Am', 'Bs', 'Ch', 'Ct', 'Da',
'Dr', 'Ds', 'El', 'En', 'Fr', 'Ge', 'Hi', 'Hs', 'Ht', 'It', 'Iv', 'Ln',
'Ma',
'Ms', 'Mu', 'PeG', 'Ps', 'Rs', 'RsS', 'Sm', 'Sp', 'Ss', 'St', 'Te', 'Tf',
'Tx') PIVOT LeaversGCSEResults.SubjectID;"

 

[KARL DEWEY]

Try it this way ---
TRANSFORM Last(LeaversGCSEResults.Grade) AS LastOfGrade
SELECT LeaversGCSEResults.PupilID, LeaversPupilData.Surname,
LeaversPupilData.Forename, LeaversPupilData.YearGroup,
LeaversPupilData.TutorGroup, LeaversPupilData.Gender,
LeaversGCSEResults.SubjectID, LeaversGCSEResults.Grade
FROM LeaversGCSEResults INNER JOIN LeaversPupilData ON
LeaversGCSEResults.PupilID = LeaversPupilData.PupilID
PIVOT LeaversGCSEResults.SubjectID In ('Ad', 'Af', 'Am', 'Bs', 'Ch', 'Ct',
'Da',
'Dr', 'Ds', 'El', 'En', 'Fr', 'Ge', 'Hi', 'Hs', 'Ht', 'It', 'Iv', 'Ln', 'Ma',
'Ms', 'Mu', 'PeG', 'Ps', 'Rs', 'RsS', 'Sm', 'Sp', 'Ss', 'St', 'Te', 'Tf',
'Tx');

 

[ridders]

Hi

I still get the missing operator error. Any other ideas?

 

[ridders]

Hi Doug

I still get the missing operator error with your suggested SQL. Any other
ideas?

 

[John Spencer]

Syntax for a crosstab is
TRANSFORM ... SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... PIVOT
WHERE and GROUP BY and HAVING are optional sections. The GROUP BY clause is
required if you have fields in the SELECT clause that are not aggregated (Sum,
Avg, First, Last, Count, etc)

I'm not totally sure what you are attempting to do, but my best guess would be
the following.

TRANSFORM Last(LeaversGCSEResults.Grade) AS LastOfGrade
SELECT LeaversGCSEResults.PupilID
, LeaversPupilData.Surname
, LeaversPupilData.Forename
, LeaversPupilData.YearGroup
, LeaversPupilData.TutorGroup
, LeaversPupilData.Gender
, LeaversGCSEResults.SubjectID
, LeaversGCSEResults.Grade
FROM LeaversGCSEResults INNER JOIN LeaversPupilData
ON LeaversGCSEResults.PupilID = LeaversPupilData.PupilID
GROUP BY LeaversGCSEResults.PupilID
, LeaversPupilData.Surname
, LeaversPupilData.Forename
, LeaversPupilData.YearGroup
, LeaversPupilData.TutorGroup
, LeaversPupilData.Gender
, LeaversGCSEResults.SubjectID
, LeaversGCSEResults.Grade
PIVOT LeaversGCSEResults.SubjectID
In ('Ad', 'Af', 'Am', 'Bs', 'Ch', 'Ct', 'Da',
'Dr', 'Ds', 'El', 'En', 'Fr', 'Ge', 'Hi', 'Hs'
, 'Ht', 'It', 'Iv', 'Ln', 'Ma', 'Ms', 'Mu', 'PeG'
, 'Ps', 'Rs', 'RsS', 'Sm', 'Sp', 'Ss', 'St',
'Te', 'Tf', 'Tx')

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

 

[KARL DEWEY]

Try this --
TRANSFORM Last(LeaversGCSEResults.Grade) AS LastOfGrade
SELECT LeaversGCSEResults.PupilID, LeaversPupilData.Surname,
LeaversPupilData.Forename, LeaversPupilData.YearGroup,
LeaversPupilData.TutorGroup, LeaversPupilData.Gender
FROM LeaversGCSEResults LEFT JOIN LeaversPupilData ON
LeaversGCSEResults.PupilID = LeaversPupilData.PupilID
GROUP BY LeaversGCSEResults.PupilID, LeaversPupilData.Surname,
LeaversPupilData.Forename, LeaversPupilData.YearGroup,
LeaversPupilData.TutorGroup, LeaversPupilData.Gender
PIVOT LeaversGCSEResults.SubjectID;