Replacing "NOT IN" with a faster criterion 1

[PaultheS]

I currently have a query that is meant to pick all customers who have not paid any of their purchases this year. A customer can have many purchases, and if they've paid even one, their number should not be selected. The query I'm using is this:

SELECT DISTINCT [CustomerID]
FROM [tbl: Sales]
WHERE [CustomerID] NOT IN (SELECT [CustomerID] FROM [tbl: Sales] WHERE Not [PaidAmount] Is Null)

The query works fine but is very slow. I've been searching through the forums and it seems like using "NOT IN" is the problem.

Does anyone have any suggestions for either speeding this query up or changing the design so I don't have to use "NOT IN"?

 

[PHV]

SELECT CustomerID FROM [tbl: Sales] GROUP BY CustomerID HAVING Max(Nz(PaidAmount,0))=0;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PaultheS]

Works like a charm! Only takes a couple of seconds now.

Thanks PHV.