Criteria across records

[Theresa]

:banghead:Hi, I have a 2010 DB with a table holding student details and another table holding the subjects in which they are enrolled.

How can I select those students who are enrolled in a group of courses. So students in all (subject A, subject B, Subject C). Not students who are enrolled only in one of the subject group.

I could do this with a series of queries, refining the group of students each time, but is their a simpler way?

 

[recyan]

Just see if below gives some guidelines :

The table
myTable

TheName TheItem
Jane Widgets
Jane Widgets
John Gadgets
John Gadgets
Rob Widgets
Rob Gadgets
Joe Bangles
Joe Gadgets
Joe Widgets

The sub-query
qryA

SELECT DISTINCT 
	myTable.TheName, 
	myTable.TheItem
FROM 
	myTable
WHERE 
	(
		((myTable.TheItem)="Widgets" 
		Or 
		(myTable.TheItem)="Gadgets")
	);

The final query
qryB

SELECT 
	qryA.TheName, 
	Count(qryA.TheItem) AS CountOfTheItem
FROM 
	qryA
GROUP BY 
	qryA.TheName
HAVING 
	(((Count(qryA.TheItem))=2));

Thanks

 

[Theresa]

Hi Recyan
That code is cute. A great idea to use the count. I wish I had thought of that!

 

[recyan]

Glad you found it helpful.

Thanks