Simple query question, please help!

[Alias]

Assuming I have the following in a course enrollment table.
CourseEnrollment(SSN, CourseID)

How do I select only the students (SSN) that have taken ALL three of these classes (CourseID = 123, 234, 345)

Thanks for any input.

 

[jeremie_ingram]

It really depends on the table layout. If all of the data is in a single table you have to normalize to get it working properly. You should have a student talble and a course table, then to connect a student to a course you should have a junciton table. The junction table would contain the keys from both tables establishing a link from student to course. In a non-normalized layout like you described, your table would contain repeated data and eaisly bloat out of controll. If it is only in two tables (students & courses) then you would end up with a many-to-many relationship wich usually is resolved with a junciton table.
Now if you cannot change the table layouts, and everything is in one table ~ it CAN still be done. Do not take this as a support for improper table structure, but what you would do is create a query that has the student field and the course field. Now use GROUP BY on the name field and COUNT on the course field. Under the course field, enter the criteria ="3" (or however many courses you have. Now the only names that appear in the query are those that are enrolled in all of the classes.

SELECT Table1.Name, Count(Table1.Course) AS CountOfCourse
FROM Table1
GROUP BY Table1.Name
HAVING (((Count(Table1.Course))=3));

Let me know if this helps

 

[Alias]

It really depends on the table layout. If all of the data is in a single table you have to normalize to get it working properly. You should have a student talble and a course table, then to connect a student to a course you should have a junciton table. The junction table would contain the keys from both tables establishing a link from student to course. In a non-normalized layout like you described, your table would contain repeated data and eaisly bloat out of controll. If it is only in two tables (students & courses) then you would end up with a many-to-many relationship wich usually is resolved with a junciton table.
Now if you cannot change the table layouts, and everything is in one table ~ it CAN still be done. Do not take this as a support for improper table structure, but what you would do is create a query that has the student field and the course field. Now use GROUP BY on the name field and COUNT on the course field. Under the course field, enter the criteria ="3" (or however many courses you have. Now the only names that appear in the query are those that are enrolled in all of the classes.

SELECT Table1.Name, Count(Table1.Course) AS CountOfCourse
FROM Table1
GROUP BY Table1.Name
HAVING (((Count(Table1.Course))=3));

Let me know if this helps

OK, it appears that I do have the tables as you describe. For example, I have a tblStudent(SSN, Fname, Lname, etc), tblCourse(CourseID, CName, etc) and finally the tblEnrollment(EnrollID, SSN, CourseID, Date, Session, etc)

The tblCourse contains 100s of courses each with a different CourseID, obviously.

Now, all I am trying to select are those students which have enrolled in CourseID 123, 234 and 345. They must all be enroll in those 3 courses, out of the 100 courses. Not 1 out of the 3 or 2 out of the 3, but all 123, 234 and 345. Some student might be enroll in other 3 courses but I don't care about those, just 123, 234, 345.

Would the query you suggested above work?

Thanks again!

 

[jeremie_ingram]

Heres an idea.

First , you need to create a union query to seperate out just the 123, 234, & 345 class enrollment.

<!-- This is the UNION query -->
SELECT tblEnrollment.EnrollmentID, tblCourse.CourseName
FROM tblCourse INNER JOIN tblEnrollment ON tblCourse.CourseID = tblEnrollment.Course
WHERE (((tblCourse.CourseName)="123"));
UNION ALL
SELECT tblEnrollment.EnrollmentID, tblCourse.CourseName
FROM tblCourse INNER JOIN tblEnrollment ON tblCourse.CourseID = tblEnrollment.Course
WHERE (((tblCourse.CourseName)="234"));
UNION ALL SELECT tblEnrollment.EnrollmentID, tblCourse.CourseName
FROM tblCourse INNER JOIN tblEnrollment ON tblCourse.CourseID = tblEnrollment.Course
WHERE (((tblCourse.CourseName)="345"));


Then you need to query that against the tblStudent to see who is enrolled in just these classes
<!-- This is the Second Query -->
SELECT tblStudent.Name, Query1.CourseName
FROM Query1 INNER JOIN (tblStudent INNER JOIN tblEnrollment ON tblStudent.SSnum = tblEnrollment.Student) ON Query1.EnrollmentID = tblEnrollment.EnrollmentID;

Now run totals on that, using group by on the name and count on the enrollment ID.

<!-- This is the Second Query Revised Once -->
SELECT tblStudent.Name, Count(Query1.CourseName) AS CountOfCourseName
FROM Query1 INNER JOIN (tblStudent INNER JOIN tblEnrollment ON tblStudent.SSnum = tblEnrollment.Student) ON Query1.EnrollmentID = tblEnrollment.EnrollmentID
GROUP BY tblStudent.Name;

Finally filter the list of names down to just those enrolled in all three classes by simply adding the criteria ="3" to the enrollment ID field in the query (where you set the count).

<!-- This is the Second Query Final Revision -->
SELECT tblStudent.Name, Count(Query1.CourseName) AS CountOfCourseName
FROM Query1 INNER JOIN (tblStudent INNER JOIN tblEnrollment ON tblStudent.SSnum = tblEnrollment.Student) ON Query1.EnrollmentID = tblEnrollment.EnrollmentID
GROUP BY tblStudent.Name
HAVING (((Count(Query1.CourseName))=3));

NOTE: Query1 in the second query is the Union Query

Let me know if this is a bit confusing or not. If the field or tbl names are not accurate, modify the above code to reflect the changes. I suggest you drop it into word or notepad and use the FIND/REPLACE function if necessary to ensure you get all changes necessary. Open the query builder and switch to SQL mode, then paste this into the SQL builder. Save it and it should function as expected. YOu may need to add fields to ensure that you get the data that you need, OR you can run the results of the second query against the student listing and pull up all the additional information you may need.

Let me know if this helps.

 

[Alias]

Heres an idea.

First , you need to create a union query to seperate out just the 123, 234, & 345 class enrollment.

<!-- This is the UNION query -->
SELECT tblEnrollment.EnrollmentID, tblCourse.CourseName
FROM tblCourse INNER JOIN tblEnrollment ON tblCourse.CourseID = tblEnrollment.Course
WHERE (((tblCourse.CourseName)="123"));
UNION ALL
SELECT tblEnrollment.EnrollmentID, tblCourse.CourseName
FROM tblCourse INNER JOIN tblEnrollment ON tblCourse.CourseID = tblEnrollment.Course
WHERE (((tblCourse.CourseName)="234"));
UNION ALL SELECT tblEnrollment.EnrollmentID, tblCourse.CourseName
FROM tblCourse INNER JOIN tblEnrollment ON tblCourse.CourseID = tblEnrollment.Course
WHERE (((tblCourse.CourseName)="345"));


Then you need to query that against the tblStudent to see who is enrolled in just these classes
<!-- This is the Second Query -->
SELECT tblStudent.Name, Query1.CourseName
FROM Query1 INNER JOIN (tblStudent INNER JOIN tblEnrollment ON tblStudent.SSnum = tblEnrollment.Student) ON Query1.EnrollmentID = tblEnrollment.EnrollmentID;

Now run totals on that, using group by on the name and count on the enrollment ID.

<!-- This is the Second Query Revised Once -->
SELECT tblStudent.Name, Count(Query1.CourseName) AS CountOfCourseName
FROM Query1 INNER JOIN (tblStudent INNER JOIN tblEnrollment ON tblStudent.SSnum = tblEnrollment.Student) ON Query1.EnrollmentID = tblEnrollment.EnrollmentID
GROUP BY tblStudent.Name;

Finally filter the list of names down to just those enrolled in all three classes by simply adding the criteria ="3" to the enrollment ID field in the query (where you set the count).

<!-- This is the Second Query Final Revision -->
SELECT tblStudent.Name, Count(Query1.CourseName) AS CountOfCourseName
FROM Query1 INNER JOIN (tblStudent INNER JOIN tblEnrollment ON tblStudent.SSnum = tblEnrollment.Student) ON Query1.EnrollmentID = tblEnrollment.EnrollmentID
GROUP BY tblStudent.Name
HAVING (((Count(Query1.CourseName))=3));

NOTE: Query1 in the second query is the Union Query

Let me know if this is a bit confusing or not. If the field or tbl names are not accurate, modify the above code to reflect the changes. I suggest you drop it into word or notepad and use the FIND/REPLACE function if necessary to ensure you get all changes necessary. Open the query builder and switch to SQL mode, then paste this into the SQL builder. Save it and it should function as expected. YOu may need to add fields to ensure that you get the data that you need, OR you can run the results of the second query against the student listing and pull up all the additional information you may need.

Let me know if this helps.

Jeremie,

Thanks for the help. I really appreciate it. I do understand what you are suggesting and after reviewing it, I think it will work. After a little bit more of playing around, I ending up using a crosstab query.

From my enrollment table, my rows for the crosstab were studentID, columns were CourseID with Column Headings 123, 234, 345 so I would just get these 3 values. Lastly, my values for the crosstab was the Max Date as I am interested in knowing when they took each of those classes.

That crosstab query would give me all the students but only the ones that taken all 3 courses had the dates filled in. Others would leave a blank.

Then I took the crosstab query and included into a select query. With the crosstab query as a subquery, I was able to select the columns 123, 234, 345 and the criteria was Is Not Null on all 3. So now, it will not show all students but only those that is not null on 123, 234, 345!!!

Just thought you might want to know! Again thanks for your help!!!