E
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,
(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,