Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query

Posted on 2004-10-25
6
Medium Priority
?
254 Views
Last Modified: 2010-04-10
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
Comment
Question by:alberte
  • 3
  • 3
6 Comments
 
LVL 96

Expert Comment

by:Lee W, MVP
ID: 12402409
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
 

Author Comment

by:alberte
ID: 12402857
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
 
LVL 96

Accepted Solution

by:
Lee W, MVP earned 1000 total points
ID: 12402915
Select EmpID, Name FROM tbl1 WHERE empid NOT IN (Select distinct empid FROM tbl3 WHERE CourseID = 4)
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:alberte
ID: 12403536
One more little thing.
 I need to students (from Tbl1) where the empid > 1330

Thank for everything.
0
 

Author Comment

by:alberte
ID: 12403579
Never mind, got it.
0
 
LVL 96

Expert Comment

by:Lee W, MVP
ID: 12403599
Aren't nested subqueries fun!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re involved with your company’s wide area network (WAN), you’ve probably heard about SD-WANs. They’re the “boy wonder” of networking, ostensibly allowing companies to replace expensive MPLS lines with low-cost Internet access. But, are they …
This article is a collection of issues that people face from time to time and possible solutions to those issues. I hope you enjoy reading it.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question