Access 2002 EXISTS / IN clause problems

[drewdog_21]

Hello all! Help please!

I have the following setup in an Access 2002 database.

A table "Students":
Employee_ID
Last_Name
First_Name
etc.

The tables "Class_Names," "Class_Schedule," and "Class_Roster" that contain class information. There is a 1:Many relationship between Students:Class_Roster.

I joined the 3 class information tables together in a query called "qryClass_Roster."

I need to construct a query that returns all Students who have successfully completed 6 specific classes, where
IdClass_Name = 1,2,3,4,5, or 6

I have begun to construct the SQL statement but have run into problems:

SELECT Students.Employee_ID, Students.Last_Name, Students.First_Name
FROM Students
WHERE Students.Employee_ID IN
(select * FROM qryClass_Roster WHERE
qryClass_Roster.Employee_ID =
Students.Employee_ID AND
qryClass_Roster.Attended = "Yes" AND
qryClass_Roster.IdClass_Name = 1);


generates the following error:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to return only one field."


If I use the EXISTS statement then the query does not filter the data correctly, because the main query returns all records when the subquery finds at least one record:

SELECT Students.Employee_ID, Students.Last_Name, Students.First_Name
FROM Students
WHERE (((Exists (select * FROM qryClass_Roster WHERE qryClass_Roster.Employee_ID = Students.Employee_ID
AND qryClass_Roster.Attended = "Yes"
AND qryClass_Roster.IdClass_Name = 1))<>False));


returns all 248 records, when it should actually return only 158.

Help? Am I approaching this issue correctly?

[drewdog_21]

I actually got that working but the SQL looks like a monkey wrote it. Can somebody help me trim this down/make it more efficient?


SELECT Students.Employee_ID, Students.Last_Name, Students.First_Name
FROM Students
WHERE Students.Employee_ID IN
(SELECT DISTINCT qryClass_Roster.Employee_ID FROM qryClass_Roster
WHERE qryClass_Roster.Employee_ID = Students.Employee_ID and qryClass_Roster.Attended = "Yes"
and qryClass_Roster.IdClass_Name = 1)
AND Students.Employee_ID IN
(SELECT DISTINCT qryClass_Roster.Employee_ID FROM qryClass_Roster
WHERE qryClass_Roster.Employee_ID = Students.Employee_ID and qryClass_Roster.Attended = "Yes"
and qryClass_Roster.IdClass_Name = 2)
AND Students.Employee_ID IN
(SELECT DISTINCT qryClass_Roster.Employee_ID FROM qryClass_Roster
WHERE qryClass_Roster.Employee_ID = Students.Employee_ID and qryClass_Roster.Attended = "Yes"
and qryClass_Roster.IdClass_Name = 3)
AND Students.Employee_ID IN
(SELECT DISTINCT qryClass_Roster.Employee_ID FROM qryClass_Roster
WHERE qryClass_Roster.Employee_ID = Students.Employee_ID and qryClass_Roster.Attended = "Yes"
and qryClass_Roster.IdClass_Name = 4)
AND Students.Employee_ID IN
(SELECT DISTINCT qryClass_Roster.Employee_ID FROM qryClass_Roster
WHERE qryClass_Roster.Employee_ID = Students.Employee_ID and qryClass_Roster.Attended = "Yes"
and qryClass_Roster.IdClass_Name = 5)
AND Students.Employee_ID IN
(SELECT DISTINCT qryClass_Roster.Employee_ID FROM qryClass_Roster
WHERE qryClass_Roster.Employee_ID = Students.Employee_ID and qryClass_Roster.Attended = "Yes"
and qryClass_Roster.IdClass_Name = 6);

[jp140768]

SELECT Students.Employee_ID, Students.Last_Name, Students.First_Name
FROM Students
WHERE Students.Employee_ID IN
(select * FROM qryClass_Roster WHERE
qryClass_Roster.Employee_ID =
Students.Employee_ID AND
qryClass_Roster.Attended = "Yes" AND
qryClass_Roster.IdClass_Name = 1);


The above wouldn't have worked, because in your sub query you are selecting mutiple fields (SELECT *), you should only return one field, so that you Where Students.Employee_id could be equated to the result.

But I'm guessing that these tables are related to each other, and therefore you should have been able to run the query using Joins, instead of sub queries???

[cjard]

Eeeek..

SELECT * FROM a WHERE a.col IN (SELECT b.col FROM b)

Should be avoided wherever possible.. Havent you people ever heard of JOINs?

SELECT * FROM a INNER JOIN b ON a.col = b.col

[jp140768]

I did suggest that joins be used.

[drewdog_21]

and I did end up using Joins, and a nifty COUNT function that I had not considered previously.

Thanks anyway folks, always learn something from the replies here.