Headache on Outer Join with Crosstab

[Epoh Rio]

I asked a question here
(http://www.microsoft.com/communitie...orts&mid=9fd2ce79-6f0d-4957-95f0-2af5891cfa0b)
and got the answer on how to make the crosstab, but since then I have changed
the database to be more functional.

Three tables

[Students]
[Attendance]
[Lessons]

Table STUDENT fields:
studentID, firstName, lastName

Table ATTENDANCE fields:
studentID, lessonID (lookup)

Table LESSONS fields:
ID, lessonDate, lessonNo (there are only 12 lessons)

My goal initially was to record each students attendance (no LESSONS table)
in the ATTENDANCE table

That was cumbersome, so I decided to add the LESSONS table and that has
significantly reduced the clutter, but I can't figure out how to the data
back in the format it was in.

So now I need to perform an Outer Join with the Crosstab query to get the
same results.

How can I get the lessons [from LESSONS] taken by the student [from
STUDENTS] joined with [ATTENDANCE] to appear as an 'X' under each lesson
taken?

Sample Data:
Table: [Students]
Fields: studentID, firstName, lastName
Data: 24, John, Smith
25, Jane, Doe

Table: [Attendance]
Fields: studentID, lessonID
Data: 25, 2
25, 3
25, 6
24, 3
24, 8


Table: [Lessons]
Fields: ID, lessonDate, lessonNo
Data: 1, 7/1/07, 1
2, 7/5/07, 2
4, 7/7/07, 3
5, 7/10/07, 6
6, 7/15/07, 8

Output:
Lessons taken to date (25):
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
X X X
Lessons taken to date (24):
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
X X


Thanks,

 

[Lord Kelvan]

SELECT STUDENTS.firstname, LESSONS.lessonno, LESSONS.lessondate
FROM (Attendance RIGHT JOIN LESSONS ON Attendance.lessonid =
LESSONS.lessonid) LEFT JOIN STUDENTS ON Attendance.Studentid =
STUDENTS.studentid
ORDER BY STUDENTS.firstname;


TRANSFORM Count(lessonstaken.lessondate) AS CountOflessondate
SELECT lessonstaken.firstname
FROM lessonstaken
GROUP BY lessonstaken.firstname
PIVOT lessonstaken.lessonno;


that sorta does what you want only problem it adds an extra line in
for a blank name but it is necessary for it to be there if there is a
lesson with no students in it or it will be hidden if you dont want
that line you need to use

TRANSFORM Count(lessonstaken.lessondate) AS CountOflessondate
SELECT lessonstaken.firstname
FROM lessonstaken
WHERE (((lessonstaken.firstname) Is Not Null))
GROUP BY lessonstaken.firstname
PIVOT lessonstaken.lessonno;

that will allow you to remove it but if a lesson has no students it
wont be displayed

hope that helps

Regards
Kelvan