By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,176 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

student prerequisites

P: n/a
Hi there,

I have a database with two tables in it. One is for student contact
information:

Contacts
ContactID
FirstName
LastName
Address
PhoneNumber
ClassID

and Courses
ClassID
ContactID
ClassDate
ClassName
ClassLocation

People who want to take Class 2 have to take Class 1 first. I want to make
a report of people who have taken Class 1 but not Class 2. The query I'm
trying is

SELECT Contacts.FirstName, Contacts.LastName, Contacts.Address,
Contacts.City, Contacts.StateOrProvince, Contacts.HomePhone,
Courses.ClassName, Courses.ClassLocation
FROM Contacts INNER JOIN Courses ON Contacts.ContactID=Courses.ContactID
WHERE Courses.ClassName = "Class 1" and Contacts.ContactID NOT IN
(SELECT Courses.ContactID FROM Courses WHERE
Courses.ClassName="Class 2")
ORDER BY Contacts.LastName;

This returns an empty set, and when I just run an alphabetical list of
contacts, I can see that there are people who have taken both Class 1 and
Class 2. What's wrong here?

take care,
robynz


Sep 30 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I created both your tables, entered dummy data, created your query by
using your SQL directly, and could not duplicate your results. I get
the results it seems you're looking for. The only suggestion I have is
that you use only your ID fields as criteria instead of the text fields
you're using now. No explanation for the results you're seeing. I even
tried establishing poor referential integrity (no PKs, duplicate
ContactIDs and ClassIDs, etc.) and had no success duplicating your
results. If your tables are as written and your SQL is as written, it
works on my system to produce a list of folks who've taken the prereq
and not the follow-on course.

Do you have ContactID as the primary key for the Contacts table, and
ContactID + ClassID as the PK for the Courses table? Are the tables
related outside the query, and if so, how? Are you running the SQL
directly as the report's RecordSource, or have you built the query in
SQL view?

Sep 30 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.