Help in my query with Where in

[bad-aries]

Hi all,
I have a query which purpose is to delete All records in table B which do not exist in Table A. The query look like this

Delete From tTempPOOutSpInst as A Where (A.ManuID and A.POOutNum) in
(Select R.ManuID, R.POOutNum from tTempPOinItems as L Right Outer Join tTempPOOutSpInst as R on L.ManuID = R.ManuID and L.POOutNum = R.POOutNum)

The ManuID and POOutNum is the primary key to check if records in tTempPOinItems and tTempPOOutSpInst are the same.

Can some one please point out what is wrong with my query?

Thanks.

 

[JamesMcS]

I think you need to use a left join, and put "is null" in the criteria of the ID field of the table you want to find unmatched records in.

 

[bad-aries]

I think you need to use a left join, and put "is null" in the criteria of the ID field of the table you want to find unmatched records in.

But right now the biggest problem is that Access complains that:

You have written a subquery that can return more than one field without using the Exists reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

Can someone please help.

 

[JamesMcS]

I'm not too hot in SQL, I prefer to design queries visually. Try that way and see what the SQL says...