How to run a query that excludes certain records?

[wes228]

I'm trying to run this query but absolutely cannot figure out how to write it. I have a table of Faculty Contacts and a separate table called "Faculty Courses" which is a table that has the Faculty ID # from Contacts as the foreign key and then all of the courses they teach in any given semester.

I want to run a query that shows me all of the faculty who are teaching in Spring 2014 (coded as 1144) OR Winter 2014 (coded as 1142) AND who are NOT teaching in Fall 2013 (coded as 1138).

In the Query Design Window I have no problem putting in the "Semester" field from the Faculty Courses field and then under Criteria putting ="1144", going down one space to put ="1142" (to make it an "or" operation). I then put in another "Semester" field next to it to make an "and" (maybe I could just type "and" after ="1144"?) and then typing <>"1138".

This however does not eliminate faculty who are teaching in Fall. It will eliminate all faculty who are ONLY teaching in Fall, however it will still return faculty who are teaching in fall, so long as they are also teaching in Winter or Spring.



How do I fix this?

Thank you!

[ssanfu]

In the criteria for "Semester", try:

<>1138 and (1144 or 1142)



Maybe you would post the SQL???

[June7]

This NOT criteria requires each record to test for a value in all other records of same table. This can be tricky. Options are subquery or domain aggregate function.

Try:

SELECT * FROM tablename WHERE (Semester=1144 OR Semester=1142) AND DLookup("Semester","tablename","Semester=1138") Is Null;

Domain aggregate functions can cause slow performance. If that is the case here, then need a subquery. Possibly:

SELECT * FROM tablename WHERE (Semester=1144 OR Semester=1142) AND NOT FacultyID IN (SELECT FacultyID FROM tablename WHERE Semester=1138);

[Dal Jeanis]

If you want to use the query design screen, and avoid SQL, then here's the trivial way to do it.

First, create a query1 that gets you all the teachers who are teaching in Spring 2014 or Winter 2014.

Second, create a query2 that gets you all the teachers who are teaching in Fall 2013.

Third, use those two queries and create a query that returns all records in query1 that are not in query2.

To do this last step, you add both those queries using the "show table" pane, link them on the faculty ID # field, and then change the query type to show all the left query that are not matched in the right query.

On the other hand, if you want to use SQL, then it will look something like this:

SELECT T1.[FacultyID#] FROM [Faculty Courses] AS T1
WHERE 
   (T1.Semester = "1144" OR T1.Semester = "1142")
AND 
   (T1.[FacultyID#] NOT IN 
      (SELECT T2.[FacultyID#] 
      FROM [Faculty Courses] AS T2
      WHERE T2.Semester = "1138")
   );

You'll have to change FacultyID# to the exact name of your field, and try it to see whether the quotes around the semester number are required or not.

[wes228]

Thanks Dal Jeanis: But how exactly do you "change the query type to show all the left query that are not matched in the right query."

Thanks again.

[June7]

Double Click on the line that joins the tables/queries in the query design view to change the join type. However, I am also not sure what Dal means by his instruction 'to show all the left query that are not matched in the right query'. That sounds more like using the NOT operator which is what Dal shows in example.

[Dal Jeanis]

Sorry - just proves that I never use the query design for complex conditions.

You'd probably use the unmatched query wizard instead. The resulting SQL wouldn't look like mine at all, but it would work. It would result in a LEFT JOIN where the right record was NULL.

That code would look something like this:

Query1:
SELECT T1.[FacultyID#] 
FROM [Faculty Courses] AS T1
WHERE (T1.Semester = "1144" OR T1.Semester = "1142"));

Query2:
SELECT T2.[FacultyID#] FROM [Faculty Courses] AS T2
WHERE (T2.Semester = "1138"));

Query3: (Find Unmatched)
SELECT Q1.[FacultyID#] 
FROM QUERY1 AS Q1 LEFT JOIN QUERY2 AS Q2
ON Q1.[FacultyID#] = Q2.[FacultyID#] 
WHERE Q2.[FacultyID#] IS Null;

Obviously, if you wanted more information from the query, you could return the fields you wanted to see in Query1, so they would be available in Query3.