• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Query

I have a course and student tracking database (Access and ASP)
Tbl1 (empId, Name) is a list of ALL students Tbl2 (couresID, Title) is a list of ALL available courses Tbl 3 (empid, couresID) is a list of  the students and the courses they registered for. So far so good, i  pull reports on who registered to a certain course and what course a student is registered for.
I got this weird request from my mangers to pull a report that would show all the student
that didn't register to specific course.

I tried unsuccessfully to create this query. I hope there is way to do it or someone
that can guide me in the right direction, like you guys dome many times in the Past.

Thank You all in advanced.
0
alberte
Asked:
alberte
  • 3
  • 3
1 Solution
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
I'm not sure if Access supports nested subqueries, but if so, I think this will work:

Select EmpID, Name FROM tbl1 WHERE empid NOT IN (Select distinct empid FROM tbl3)
0
 
alberteAuthor Commented:
Intresting. I would like to refine this query to show all the students that that are not registred to a specific course (say only  where tbl2 course ID = 4).
Thanks.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Select EmpID, Name FROM tbl1 WHERE empid NOT IN (Select distinct empid FROM tbl3 WHERE CourseID = 4)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
alberteAuthor Commented:
One more little thing.
 I need to students (from Tbl1) where the empid > 1330

Thank for everything.
0
 
alberteAuthor Commented:
Never mind, got it.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Aren't nested subqueries fun!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now