Your problem is a common generic performance problem

-- old syntax used before SQL 92 outer joins available
SELECT *
FROM A
WHERE A.id NOT IN (SELECT id FROM B)

-- new syntax using SQL 92 joins - can runs 100/1000s of times faster
SELECT *
FROM A LEFT JOIN B ON B.id = A.id
WHERE B.id IS NULL

The theory is simple do an outer join using all rows from table A and filter
them to show only rows where B.id is NULL (= no corresponding row in B)

In your example

SELECT Id, LOC, AGENCY, BATCH
FROM tblRows R
LEFT JOIN (SELECT DISTINCT B.AGENCY
FROM tblRows AS A, tblRows AS B
WHERE A.LOC = "B"
AND B.AGENCY = A.AGENCY
AND B.BATCH = A.BATCH) R2
ON R.AGENCY = R2.AGENCY
WHERE R2.AGENCY IS NULL

NOTE: I did not test the queries so - if it does not work it just means that
you need a RIGHT JOIN, as I do not always remember which way round the
syntax goes - the logic however is correct.

Hope this help
Julian 8^)

Change to a JOIN,
"bala" <balkiir@gmail.com> wrote in message
news:1110334059.973692.269180@z14g2000cwz.googlegr oups.com...
> hi!!!
>
> the following is the sql which is veri slow cos of the 'Not In' clause,
> would appreciate if u anyone can suggest any other way to bring about
> the same result
>
> SELECT Id, LOC, AGENCY, BATCH
> FROM tblRows
> WHERE tblRows.AGENCY NOT IN (SELECT DISTINCT B.AGENCY
> FROM tblRows AS A, tblRows AS B
> WHERE A.LOC = "B"
> AND B.AGENCY = A.AGENCY
> AND B.BATCH = A.BATCH)
>
> tblRows
> ID LOC AGENCY BATCH
> 1 B 1000 WAD
> 2 R 1000 WAD
> 3 B 1010 QAD
> 4 B 1020 WAD
> 5 R 1020 WAD
> 6 R 1030 RRR
> 7 I 1030 RRR
> 8 V 1030 RRR
> 9 B 1040 UIA
> 10 R 1040 UIA
> 11 I 1040 UIA
> 12 V 1040 UIA
>
> the subquery is to return the rows with LOC = B. the above query as the
> whole should return the rows where LOC <> b and also must exclude rows
> belong to the LOC = B subset (that is for example the first two rows
> with ID 1 and 2. the first row with ID 1 has LOC as B, Agency as 1000
> and BATCH as WAD. the second row with Id 2 has LOC as R as the same
> AGENCY and BATCH as first row with ID 1 so is the subset of first row.
> similarly row with Id's 4 and 5.
>
> the above query must return the following the rows (that is we can say
> the orphan rows which doesn't have LOC AS B nor belongs to the B's
> subset
>
> 6 R 1030 RRR
> 7 I 1030 RRR
> 8 V 1030 RRR
>
> hope i am clear in my explanation and would appreciate if someone can
> point me in the right direction. the reason for posting this in ms
> access group is because this is going to be a query in MS Access.
>
> regards
> bala
>