Missing records on a CrossTab

J

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top