Solved

Query

Posted on 2004-10-25
250 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
Question by:alberte
    6 Comments
     
    LVL 95

    Expert Comment

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

    Accepted Solution

    by:
    Select EmpID, Name FROM tbl1 WHERE empid NOT IN (Select distinct empid FROM tbl3 WHERE CourseID = 4)
    0
     

    Author Comment

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

    Thank for everything.
    0
     

    Author Comment

    by:alberte
    Never mind, got it.
    0
     
    LVL 95

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Social networking sites such as Facebook have become an immensely popular way to connect with friends, coworkers, and relatives on the internet.  Most are very user-friendly and provide methods to e-mail, chat, share pictures and videos, and even se…
    I imagine hoaxes have been around since people learned to speak, scams since bartering, chain letters since Pony Express, spam and embedded viruses since email was invented in 1971. Still all those things are alive and well today despite many techno…
    After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
    After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now