NOT IN (SQL) or Find Unmatched (+ other stuff)

Big issue here trying to build a query that lists records whose serial number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands anywhere?

Make sure you eliminate any NULLs in the sub-query.

WHERE pd.SERIAL_NUMBER NOT IN
(SELECT SERIAL_NUMBER
FROM INSTALL
WHERE Serial_Number is NOT NULL)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Farkyss wrote:
Big issue here trying to build a query that lists records whose serial number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands anywhere?

SELECT DISTINCT Install.SERIAL_NUMBER
FROM Install LEFT JOIN PD
ON Install.SERIAL_NUMBER = PD.SERIAL_NUMBER
WHERE PD.SERIAL_NUMBER Is Null
ORDER BY 1;

Select Install.SERIAL_NUMBER
FROM Install LEFT JOIN PD
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Farkyss" wrote:

Big issue here trying to build a query that lists records whose serial number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands anywhere?

Tried this. Access still ignores the NOT IN clause

"John Spencer" wrote:

Make sure you eliminate any NULLs in the sub-query.

WHERE pd.SERIAL_NUMBER NOT IN
(SELECT SERIAL_NUMBER
FROM INSTALL
WHERE Serial_Number is NOT NULL)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Farkyss wrote:
Big issue here trying to build a query that lists records whose serial number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands anywhere?
.

Not sure this will achieve what I want. This in mind, find below the full SQL
code:
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID,
pa.DESCRIPTION, c.BUSINESS_NAME, l.BUILDING_NAME, l.STREET, l.LOCALITY,
l.POSTAL_TOWN, l.COUNTY, l.PHONE
FROM (((PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID) INNER JOIN CUSTOMER AS c ON
po.CUSTOMER_ID=c.CUSTOMER_ID) INNER JOIN LOCATION AS l ON
c.BILLING_ADDRESS=l.LOCATION_ID
WHERE pd.SERIAL_NUMBER NOT IN (SELECT INSTALL.SERIAL_NUMBER FROM INSTALL)
AND pd.SERIAL_NUMBER IS NOT NULL;


"Jerry Whittle" wrote:

SELECT DISTINCT Install.SERIAL_NUMBER
FROM Install LEFT JOIN PD
ON Install.SERIAL_NUMBER = PD.SERIAL_NUMBER
WHERE PD.SERIAL_NUMBER Is Null
ORDER BY 1;

Select Install.SERIAL_NUMBER
FROM Install LEFT JOIN PD
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Farkyss" wrote:

Big issue here trying to build a query that lists records whose serial number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands anywhere?

On Wed, 24 Feb 2010 08:58:07 -0800, Farkyss
wrote:

Big issue here trying to build a query that lists records whose serial number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands anywhere?

The NOT IN syntax works fine for me. Could you post the actual complete SQL of
your query? Does it have other criteria, particularly OR criteria?
--

John W. Vinson [MVP]

SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID,
pa.DESCRIPTION, c.BUSINESS_NAME, l.BUILDING_NAME, l.STREET, l.LOCALITY,
l.POSTAL_TOWN, l.COUNTY, l.PHONE
FROM (((PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID) INNER JOIN CUSTOMER AS c ON
po.CUSTOMER_ID=c.CUSTOMER_ID) INNER JOIN LOCATION AS l ON
c.BILLING_ADDRESS=l.LOCATION_ID
WHERE pd.SERIAL_NUMBER NOT IN (SELECT INSTALL.SERIAL_NUMBER FROM INSTALL)
AND pd.SERIAL_NUMBER IS NOT NULL;




"John W. Vinson" wrote:

On Wed, 24 Feb 2010 08:58:07 -0800, Farkyss
wrote:

Big issue here trying to build a query that lists records whose serial number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands anywhere?

The NOT IN syntax works fine for me. Could you post the actual complete SQL of
your query? Does it have other criteria, particularly OR criteria?
--

John W. Vinson [MVP]
.

If Serial_Number are alphanumerical, can you try :


.... where TRIM(pd.serial_number) not in (select TRIM(serial_number) from
install) and ...


Vanderghast, Access MVP


"Farkyss" wrote in message
...
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID,
pa.DESCRIPTION, c.BUSINESS_NAME, l.BUILDING_NAME, l.STREET, l.LOCALITY,
l.POSTAL_TOWN, l.COUNTY, l.PHONE
FROM (((PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID) INNER JOIN CUSTOMER AS c ON
po.CUSTOMER_ID=c.CUSTOMER_ID) INNER JOIN LOCATION AS l ON
c.BILLING_ADDRESS=l.LOCATION_ID
WHERE pd.SERIAL_NUMBER NOT IN (SELECT INSTALL.SERIAL_NUMBER FROM INSTALL)
AND pd.SERIAL_NUMBER IS NOT NULL;




"John W. Vinson" wrote:

On Wed, 24 Feb 2010 08:58:07 -0800, Farkyss
wrote:

Big issue here trying to build a query that lists records whose serial
number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear
in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are
just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands
anywhere?

The NOT IN syntax works fine for me. Could you post the actual complete
SQL of
your query? Does it have other criteria, particularly OR criteria?
--

John W. Vinson [MVP]
.

The values for serial_number in the INSTALL table are looked up from PD, so
physically can't be any different

"vanderghast" wrote:

If Serial_Number are alphanumerical, can you try :


... where TRIM(pd.serial_number) not in (select TRIM(serial_number) from
install) and ...


Vanderghast, Access MVP


"Farkyss" wrote in message
...
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID,
pa.DESCRIPTION, c.BUSINESS_NAME, l.BUILDING_NAME, l.STREET, l.LOCALITY,
l.POSTAL_TOWN, l.COUNTY, l.PHONE
FROM (((PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID) INNER JOIN CUSTOMER AS c ON
po.CUSTOMER_ID=c.CUSTOMER_ID) INNER JOIN LOCATION AS l ON
c.BILLING_ADDRESS=l.LOCATION_ID
WHERE pd.SERIAL_NUMBER NOT IN (SELECT INSTALL.SERIAL_NUMBER FROM INSTALL)
AND pd.SERIAL_NUMBER IS NOT NULL;




"John W. Vinson" wrote:

On Wed, 24 Feb 2010 08:58:07 -0800, Farkyss
wrote:

Big issue here trying to build a query that lists records whose serial
number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear
in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are
just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands
anywhere?

The NOT IN syntax works fine for me. Could you post the actual complete
SQL of
your query? Does it have other criteria, particularly OR criteria?
--

John W. Vinson [MVP]
.

(but no, tried it and no difference)
Would note that my test case involves one record in the INSTALL table that
shares a serial_number with the PORD pd table, so I know that record should
be omitted.
If I remove the subquery from the NOT IN condition and instead replace it
with just a list ("val1","val2") where one of the values is this known
serial_number, then it triggers correctly, so is it maybe that Access 2007 is
not allowing the use of a subquery in a NOT IN clause?
"vanderghast" wrote:

If Serial_Number are alphanumerical, can you try :


... where TRIM(pd.serial_number) not in (select TRIM(serial_number) from
install) and ...


Vanderghast, Access MVP


"Farkyss" wrote in message
...
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID,
pa.DESCRIPTION, c.BUSINESS_NAME, l.BUILDING_NAME, l.STREET, l.LOCALITY,
l.POSTAL_TOWN, l.COUNTY, l.PHONE
FROM (((PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID) INNER JOIN CUSTOMER AS c ON
po.CUSTOMER_ID=c.CUSTOMER_ID) INNER JOIN LOCATION AS l ON
c.BILLING_ADDRESS=l.LOCATION_ID
WHERE pd.SERIAL_NUMBER NOT IN (SELECT INSTALL.SERIAL_NUMBER FROM INSTALL)
AND pd.SERIAL_NUMBER IS NOT NULL;




"John W. Vinson" wrote:

On Wed, 24 Feb 2010 08:58:07 -0800, Farkyss
wrote:

Big issue here trying to build a query that lists records whose serial
number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear
in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are
just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands
anywhere?

The NOT IN syntax works fine for me. Could you post the actual complete
SQL of
your query? Does it have other criteria, particularly OR criteria?
--

John W. Vinson [MVP]
.