Luke Luke - 1 year ago 81
SQL Question

MS Access, how to replace a saved query by the full query in a subquery

I have 3 tables:

1 - tblMembers_Info

2 - a junction table

3 - tblCourses

I need to query the members who haven't done a specific course.

After trying to do it manually I gave MS Access "Query Wizard" a try. I ended up with :

A saved query as Query1:
// That one query who did the course

SELECT tblMembers_Info.*, tblCourses.CourseName
FROM tblMembers_Info
(tblCourses INNER JOIN tblMembers_Courses
ON tblCourses.IDCourses = tblMembers_Courses.IDCourses)
ON tblMembers_Info.Members_ID = tblMembers_Courses.Members_ID
WHERE (tblCourses.CourseName) In ('NameOftheCourse');

2nd query using the saved Query1:

SELECT tblMembers_Info.Members_ID, tblMembers_Info.FirstName, tblMembers_Info.LastName
FROM tblMembers_Info
LEFT JOIN [Query1]
ON tblMembers_Info.[Members_ID] = Query1.[Members_ID]
WHERE (((Query1.Members_ID) Is Null));

How can I replace the Query1 in the second query with the full query instead of using a QueryDef (the saved query "Query1")?

Also, there's a better way for sure to write that query, I would really appreciate any help.

Answer Source

You can simply replace LEFT JOIN [Query1] with LEFT JOIN (...) AS [Query1] where ... should be the SQL of the first query, without the ending ;.

But I think in your specific case the use of NOT IN might give a better performance to get the same results:

SELECT     tblMembers_Info.Members_ID, tblMembers_Info.FirstName, tblMembers_Info.LastName 
FROM       tblMembers_Info 
WHERE      tblMembers_Info.[Members_ID] NOT IN (
    SELECT     tblMembers_Info.[Members_ID]
    FROM       ((tblMembers_Info 
    INNER JOIN tblMembers_Courses
            ON tblMembers_Info.Members_ID = tblMembers_Courses.Members_ID)
    INNER JOIN tblCourses
            ON tblCourses.IDCourses = tblMembers_Courses.IDCourses)
    WHERE      tblCourses.CourseName = 'NameOftheCourse'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download