Consinder using NOT EXISTS and creating helpful indexes. For example:

CREATE TABLE tblRows
(
ID int NOT NULL,
LOC char(1) NOT NULL,
AGENCY int NOT NULL,
BATCH char(3) NOT NULL
)
INSERT INTO tblRows VALUES(1,'B',1000,'WAD')
INSERT INTO tblRows VALUES(2,'R',1000,'WAD')
INSERT INTO tblRows VALUES(3,'B',1010,'QAD')
INSERT INTO tblRows VALUES(4,'B',1020,'WAD')
INSERT INTO tblRows VALUES(5,'R',1020,'WAD')
INSERT INTO tblRows VALUES(6,'R',1030,'RRR')
INSERT INTO tblRows VALUES(7,'I',1030,'RRR')
INSERT INTO tblRows VALUES(8,'V',1030,'RRR')
INSERT INTO tblRows VALUES(9,'B',1040,'UIA')
INSERT INTO tblRows VALUES(10,'R',1040,'UIA')
INSERT INTO tblRows VALUES(11,'I',1040,'UIA')
INSERT INTO tblRows VALUES(12,'V',1040,'UIA')
GO

ALTER TABLE tblRows
ADD CONSTRAINT PK_tblRows PRIMARY KEY(ID)
GO

CREATE INDEX Index1 ON tblRows(LOC, AGENCY, BATCH)
GO

SELECT Id, LOC, AGENCY, BATCH
FROM tblRows B
WHERE NOT EXISTS
(
SELECT *
FROM tblRows AS A
WHERE A.LOC = 'B'
AND B.AGENCY = A.AGENCY
AND B.BATCH = A.BATCH
)


--
Hope this helps.

Dan Guzman
SQL Server MVP

"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
>