filter using queries

  • Thread starter Thread starter priya
  • Start date Start date
P

priya

Hi

I have a data table A with several fields including cust #
and part #. I have another table B with cust # and part #.
I would like to create a query so that the output is all
line items from table A where cust # <> cust # in table B
AND part # <> part # in table B.

Not in doesnot seem to work.
((([Table A].[Cust #]) Not In ("[Table B].[Cust
#]","[Table B]") and (([Table A].[Cust #]) Not In ("[Table
B].[Cust #]","[Table B]") )

Any help is highly appreciated.

Thanks
Priya
 
Dear Priya:

The thing you need to put inside the parens needs to be a query that
returns the rows you don't want, like:

[Table A].[Cust #] NOT IN(SELECT [Cust #] FROM [Table B])

and

[Table A].[Part #] NOT IN(SELECT [Part #] FROM [Table B])

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The IN criteria is incorrectly used. Try something like this:

SELECT *
FROM TableA
WHERE CustNo Not In (SELECT CustNo FROM TableB)
AND PartNo Not In (SELECT PartNo FROM TableB)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSpgJ4echKqOuFEgEQJVFwCgtEJ/qerWkpGL++H7OmkDxabrXBIAoIIU
ioiig8UICnwT3bfv+rGrGK+P
=HmNV
-----END PGP SIGNATURE-----
 
Back
Top