cross tab query outer join

[inungh]

I tried to have a cross tab query out join like following data:

tblstudent

Student ID
1
2
3

tblExam
Student ID, Exam Date, Exam ID
1 10/01/2009 1

I would like have follwoing result

Stduent ID, Exam1 , Exam 2, Exam Count
1 1 0 1
2 0 0 0
3 0 0 0

For some reason, the query does not show Stduent 2 and Student 3,
since there is no exam in the exam table.

Can I do this in one cross tab query?

Your help is great appreciated,

 

[vanderghast]

Make a query like:

SELECT *
FROM tblStudent LEFT JOIN tblExam
ON tblStudent.[Student ID] = tblExam.[Student ID]


And make your crosstab query based on that query you would have just saved.

--Suggestions:
Avoid using spaces in field names.
Using prefixes in SQL is not appropriate: there is no possibility to use a
field name where a table name is required. Using prefixes just make longer
statements, and harder to read. If any convention is still prefered, try
using the plural for table: Students, and singular for the field:
studentID. Additional example, your tblExam is probably best labeled
StudentsExams since it probably hold a many to many relation between the pk
of tables Students and Exams. But sure, in the end, it is you, no me, who
have to work with them, so, do as it pleases you.



Vanderghast, Access MVP