How to display the same field in a query with different data

[SarahBeth]

I am trying to mock up a simple version of a solution to a problem and I can’t think of the life of me how to solve it
I have the following table



Each student is on two courses. I want to create a query that will display the students name and email from the student table (linked by ID to StuID) along with their non Knitting CourseName, Start and end date for that and then the knitting courseName and the end date for that.

[lfpm062010]

I can only see Courses table. I can try to come up with some base on your description.

SELECT STUDENT.STUDENTSNAME, STUDENT.STUDENTEMAIL, COURSES.COURSENAME, COURSES.COURSESTART, COURSES.COURSEEND
FROM STUDENT INNER JOIN COURSES ON STUDENT.ID = COURSES.STUID
WHERE (((COURSES.COURSENAME)<>"Knitting"));

[SarahBeth]

Thanks but that's not quite what I mean. Maybe I haven't explained clearly enough.

What I would like to display would be the following

StudentName | StudentEmail | CourseTitle (whatever non knitting course they are on) | Course Start date (for that course) | Course End date (for that course) | Course End date for the knitting course.

(the info I have given here is just a mock up but to give some context what I am trying to display is the PhD course a student is on as well as info about when their Transferrable Skills course is also finishing.
Hope that makes sense

[June7]

Why do you need them side-by-side? Could just build a report that orders and groups records by StuID.

Otherwise, try:

SELECT StudentName, Email,
Max(IIf(CourseName<>"Knitting", CourseName, Null)) AS PHDCourse,
Max(IIf(CourseName<>"Knitting", CourseStart, Null)) AS PHDStart,
Max(IIf(CourseName<>"Knitting", CourseEnd, Null)) AS PHDEnd,
Max(IIf(CourseName="Knitting", CourseEnd, Null)) AS KnittingEnd
FROM Student INNER JOIN Courses ON Student.ID = Courses.StuID GROUP BY StudentName, Email;