Crosstab query error

  • Thread starter Thread starter ridders
  • Start date Start date
R

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
 
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;"
 
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');
 
Hi Doug

I still get the missing operator error with your suggested SQL. Any other
ideas?
 
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
 
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;
 
Back
Top