Access query that retrieves records not in a table

[vguna]

Hi,

Hi,

I have to write a query that can display records that are not present in one table. For eg. I have a course tablle, a student table and a registered course table. The course table has the list of all courses. The student table has the list of all students. The registered course table holds the student ids and the course ids corresponding to the courses registered by the particular student. I would like to write a query that can display all the courses that havent been registered by a particular student. Can anyone help me with this.

Course Table
id course name instructor
1 Database design David Mok
2 E-Commerce Anne Bradley
3 Networks Roberta Stanley

Student Table
id student name status
101 Kerri Kenney senior
201 Anderson Mason freshman
311 Calvin Stenison senior

Registration
id student id course id
1001 201 3
1002 201 1
1003 101 2
1003 101 1
1004 311 3

I would like to display all the courses that have not been registered by student id 311(which would be course id 1 and 2).
Can someone help me with this?

Thanks in advance!!!

 

[TheAceMan1]

How are ya vguna . . . .

Just what are the relationships between the tables?

See Ya! . . . . . .

 

[lupins46]

Select * from courses left join (Select * from [registered course]where studentid = [Enter StudentId]) as s
on course.courseid = s.courseid
where studentid is null

 

[TheAceMan1]

lupins46 . . . .

i Should have bee more specific. I mean in your [blue]Relationships Window[/blue], which can be different from the query . . .

StandingBy . . . .

See Ya! . . . . . .

 

[TheAceMan1]

vguna . . . I think I have what ya want. Sorry to get so late.

Been awhile since I wrote a query to find unmatched, but here goes.

First, in query design view, make a query to return the fields [blue]StudentID & CourseID[/blue] from the [blue]Registration Table[/blue]. Lets call it qryReg for now. [purple]Note: you'll have to decide how your gonna set the criteria for the StudentID![/purple]

Next, you can use the SQL statement below in a forms [blue]RecordSource[/blue] or in SQL view in query design.

[blue]SELECT tblCourse.CourseID, 
             tblCourse.Name, 
             tblCourse.Instructor
FROM tblCourse 
LEFT JOIN qryReg 
ON tblCourse.CourseID = qryReg.CourseID
WHERE (((qryReg.CourseID) Is Null));[/blue]

There's a way to include the SQL of qryReg within the SQL statement but I havn't found it in my notes yet (sooooo many). Mean while, this should hold ya . . . .

See Ya! . . . . . .