Finding records that are not contianed in another query

Question

I have two table that are related to each other: QUOTERun and QUOTERunResults. QUOTERun contains all of the items that are needed for a particular quote. QUOTERunResults contains records for all of the vendor responses. I have a program that analyzes those responses and selects the vendor that is the best fit and set a field called Preffered to true. Some times none of the vendors will work. I need to create a list of all the records in QUOTERun that don't have a matching record in QUOTERunResults that is marked as preffered.

Here is what I have so far:

Set rs1 = CurrentDb.OpenRecordset("SELECT ReferenceNumber FROM [QUOTERun] WHERE RunID = " & TempRunID, dbOpenDynaset)
Set rs2 = CurrentDb.OpenRecordset("SELECT ReferenceNumber, Preffered FROM [QUOTERunResults] " & _
                                  "WHERE RunID = " & TempRunID & " AND Preffered = True", dbOpenDynaset)
If (rs2.RecordCount = 0) Then
   TempListNotComplete = "NONE OF THE VENDOR RESPONSES HAD ENOUGH STOCK. NO RECORDS HAVE BEEN MARKED AS PREFFERED."
ElseIf (rs1.RecordCount <> 0) Then
   rs1.MoveFirst
   Do While rs1.EOF <> True
      rs2.FindFirst "ReferenceNumber = " & rs1.Fields("[ReferenceNumber]").value
      If (rs2.Fields("[ReferenceNumber]").value <> rs1.Fields("[ReferenceNumber]").value) Then
         If (TempListNotComplete = "") Then 
            TempListNotComplete = rs1.Fields("[ReferenceNumber]").value 
     Else 
            TempListNotComplete = TempListNotComplete & ", " & rs1.Fields("[ReferenceNumber]").value
     End If
      End If
      rs1.MoveNext
   Loop
End If

is there a more efficient way of doing the above?

Answer 1

You can create a query in SQL view of the query design window:

SELECT a.ReferenceNumber 
FROM
    (SELECT ReferenceNumber 
     FROM [QUOTERun] 
     WHERE RunID = [Enter TempRunID]) a
LEFT JOIN 
    (SELECT ReferenceNumber, Preffered 
     FROM QUOTERunResults 
     WHERE RunID = [Enter TempRunID] AND Preffered = True) b
On a.ReferenceNumber=b.ReferenceNumber
WHERE b.ReferenceNumber Is Null

The above should show all records in the first derived table that are not in the second derived table.