Subquery appropriate?

[MasterLu]

Hi - It seems like it should be simple... but I can't figure this out!

The code I have been working on, but may be entirely on wrong track:
SELECT DISTINCT ORDER_NUMBER, TESTING_DATE, Category, Passing_Score
FROM qry_Database
WHERE (((TESTING_DATE)=#5/8/2004 8:30:0#) AND ((Category)="Big")) AND ((ORDER_NUMBER) not in (SELECT ORDER_NUMBER FROM qry_Database WHERE Passing_Score=70))

qry_database:
01 5/8/04 Big 70
01 5/8/04 Big 75
02 5/8/04 Big 80
02 5/8/04 Big 75
02 5/8/04 Big 75
03 5/8/04 Big 70
04 5/8/04 Big 80
04 5/8/04 Big 75
05 5/8/04 Big 80

i'm only trying to derive the ones marked below with * by it, which means any Order Numbers that do not include an item that requires passing_score=70:
01 5/8/04 Big 70
01 5/8/04 Big 75
02 5/8/04 Big 80 *
02 5/8/04 Big 75 *
03 5/8/04 Big 70
04 5/8/04 Big 70
04 5/8/04 Big 75
04 5/8/04 Big 80
05 5/8/04 Big 80 *

any help GREATLY appreciated!!!

 

[MasterLu]

Hi - actually, it does work and gives me the results I need... but it is extremely slow which is why I thought it wasn't working at first. Any tips on how to speed it up? I really appreciate it!!!

 

[JonFer]

Create a query that gives you the Distinct Order numbers WITH a score of 70 for the desired test date and category. Then drop that query into your existing query and join on the Order number. Make it an Outer Join (double-click on the join line and include all from qry_Database) and then add a criteria that Order number from the NEW query "Is Null". That should speed it up considerably.

You can also put nonunique indexes on Testing_Date, Category, and Order_Number. This will help if there are many records that are excluded based on these criteria.