Missing records on a CrossTab

[Jeffrey Marks]

This is an existing query to cross tab grades for a particular
student:

TRANSFORM First([08-09 EHS Course Grade].[Final Mark]) AS [LastOfFinal
Mark]
SELECT [09-10 EHS GPA-Absence].[Pupil Number]
FROM ([09-10 EHS GPA-Absence] INNER JOIN [08-09 EHS Course Grade] ON
[09-10 EHS GPA-Absence].[Pupil Number]=[08-09 EHS Course Grade].[Pupil
Number]) INNER JOIN [Course Codes] ON [08-09 EHS Course Grade].[Course
Code]=[Course Codes].[Course Code]
GROUP BY [09-10 EHS GPA-Absence].[Pupil Number]
PIVOT [Course Codes].[Course Name] In
("Math","English","Science","Social Studies");


My problem is that when a student does not have all 4 classes (math,
english, science, social studies) no record appears in the cross tab.
So if student 111111 takes math, english, science and PE, I will not
get a record in the query. I would still like to see a record, just
with nothing in the social studies field. Is that possible?

thanks

jeff

 

[John W. Vinson]

This is an existing query to cross tab grades for a particular
student:

TRANSFORM First([08-09 EHS Course Grade].[Final Mark]) AS [LastOfFinal
Mark]
SELECT [09-10 EHS GPA-Absence].[Pupil Number]
FROM ([09-10 EHS GPA-Absence] INNER JOIN [08-09 EHS Course Grade] ON
[09-10 EHS GPA-Absence].[Pupil Number]=[08-09 EHS Course Grade].[Pupil
Number]) INNER JOIN [Course Codes] ON [08-09 EHS Course Grade].[Course
Code]=[Course Codes].[Course Code]
GROUP BY [09-10 EHS GPA-Absence].[Pupil Number]
PIVOT [Course Codes].[Course Name] In
("Math","English","Science","Social Studies");


My problem is that when a student does not have all 4 classes (math,
english, science, social studies) no record appears in the cross tab.
So if student 111111 takes math, english, science and PE, I will not
get a record in the query. I would still like to see a record, just
with nothing in the social studies field. Is that possible?

thanks

jeff

Yes; but you'll need an Outer Join (Left or Right as appropriate) rather than
the Inner Join. I'm not certain of your table structure so this might not be
correct but try

TRANSFORM First([08-09 EHS Course Grade].[Final Mark]) AS [LastOfFinal
Mark]
SELECT [09-10 EHS GPA-Absence].[Pupil Number]
FROM ([09-10 EHS GPA-Absence] LEFT JOIN [08-09 EHS Course Grade] ON
[09-10 EHS GPA-Absence].[Pupil Number]=[08-09 EHS Course Grade].[Pupil
Number]) INNER JOIN [Course Codes] ON [08-09 EHS Course Grade].[Course
Code]=[Course Codes].[Course Code]
GROUP BY [09-10 EHS GPA-Absence].[Pupil Number]
PIVOT [Course Codes].[Course Name] In
("Math","English","Science","Social Studies");

This will return all records in [09-10 EHS GPA-Absence] whether or not there
is a record in [08-09 EHS Course Grade].
--

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