query one to many relationship

N

needlewomyn

I am trying to build a query that I originally thought would be easy but have
had no luck muddling through to a solution. I appreciate any help you can
give. I can work pretty easily with the query design grid, but am an
absolute novice at the SQL langauge.

I have a table called "students" and another called "courses." They are
linked in a one-to-many relationship, with "studentID" as the primary key /
linking field. For every record in the parent table ("students"), there are
multiple records in "courses" (the courses they have actually completed).

What I need is a query that will show me which students have not taken a
particular course, "requ1" (that is, I need a query that will show me which
records from the parent table do not have a record with a particular
critieria in the child table). I can easily write a query that shows which
students have taken "requ1," but not which ones haven't.

I've looked around this site a bit and think I might need to do something
with an outer join, but I'm fuzzy on what that means and how to to do.

Thanks.
 
A

Allen Browne

To track the data you are talking about you need these tables at a minimum.

Student table: one record for each person
StudentID AutoNumber primary key (PK)
Surname Text etc.

Unit table: one record for each kind of unit offered.
UnitID Text (3) PK.
UnitName Text Name of this unit

Course: one record for each time a course is run.
CourseID AutoNumber PK
UnitID Text (3) foreign key (FK) to Unit.UnitID
StartDate Date/Time date this course begins

CourseDone: one record for each person in each course.
CourseID Number FK to Course.CourseID
StudentID Number FK to Student.StudentID


You can now create a query based on the Course and CourseDone tables.
Depress the Total icon on the toolbar (upper Sigma icon).
Group By the StudentID field, and the CourseID field.
Optionally, choose Max of the StartDate field.
Save the query with the name "qryCourseDone".

Now create another query into your Student table and the Unit table. There
will be no line joining the 2 tables. This gives you every possible
combination of Emplyee and Unit. Save with the name "qryStudentUnit".

Now create another query that uses both queries as the input "tables". You
should see 2 lines joining the 2 "tables" in the upper pane of the query
design window, on the StudentID fields, and the UnitID fields. Double-click
these lines. Access opens a dialog with 3 options. Choose the one that says:
All records from qryStudentUnit, and any records from ...
This query will show every person and every unit, with the last date they
did the course, and the date will be blank if they never did the course.
Save this query.

Use the 3rd query as the RecordSource for your report.
 
Top