Need help with a query

[AnnIsabelle]

Hi,
I have a problem with defining a query.
I have a database with a table tblStudents and a table tblCourses. Between these two there is a junction table tblStudCour, cause students can choose multiple courses and courses can be chosen by multiple students. The primary key are two fields (IDStud and IDCourse).

My query has to resolve a problem with timetables of some courses. If course 10 ends at 10h00 and course 5 starts at 9h30 we have a problem. So in order to find out how many students are affected by this timetable, we want to use this query.

First I want the surname and firstname of all students who follow course 10. From that result I need to know who also follows course 5 (or for that matter any given course number).

I want to do the last step by using a parameter query, so if I need to know if there's a problem with course 5 I enter 5. If I want to know if there's a problem with course 6, I enter 6 in the parameterquery.

I don't know how to do this in Access. Choosing "or" in the criteria of the query window gives me everyone who choose 10 or e.g. 5. And using "and" returns me 0 records, although there are students that fulfill the criteria.

Desperately in need of help,
AnnIsabelle

 

[Golom]

This just gives you the student ID's of those students that are in both courses. You can join this to other tables if you want names.

SELECT   IDStud
FROM     tblStudCour
WHERE    IDCourse IN (5, 10)
GROUP BY IDStud
HAVING   Count(*) = 2

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[AnnIsabelle]

Thanks a lot, this works perfect!

AnnIsabelle