The PORD table contains serial number/quantity lines in a PURCHASE ORDER
table (as each purchase order can be of several different items, each in need
of their own serial number)
The INSTALL table contains records of certificates sent as proof that a part
has been fitted by an engineer. The important part of that record is the
serial number.
When entering details of an install certificate, I limited the choice of
serial numbers to those that actually existed - this would reduce errors
later on,
The code listed below is a shorten version i need for a QUERY to pull out
details from Purchase Order/PORD/PART tables for items whose serial numbers
DO NOT appear in the INSTALL tables. This information basically tells us what
items have been sold that don't have installation certificates for

The links to JPG give test data and screenshots of the results

"orange via AccessMonster.com" wrote:

Farkyss wrote:
Updating this question with a refresh:

http://www.clanimperium.net/1.JPG
http://www.clanimperium.net/2.JPG
http://www.clanimperium.net/3.JPG
http://www.clanimperium.net/4.JPG
(capital JPG is important)

1 shows results of
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION
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;
(no WHERE clause at all - results as expected)

2 shows results of the subquery on it's own
SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL;
(results as expected)

3 shows results of
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION
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;
WHERE pd.SERIAL_NUMBER NOT IN ('999789');
(manual NOT IN clause - results as expected - 999789 excluded)

4 Shows results of the full query
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION
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
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL WHERE
SERIAL_NUMBER IS NOT NULL);
(Results NOT as expected 999789 isn't excluded)

TRIMming the subquery doesn't help. VAL can't be used because serial_number
can be alphabetic. Essentially NOT IN doesn't work with a list generated from
a subquery. Could someone attempt to reproduce these results? Any comment
from Microsoft from official? Anyone with an alternative method? This whole
project is frozen until this is fixed.

Perhaps you could tell us what you're trying to do in english before going
directly to code.
Also, some sample data or data base along with any relationships would be
helpful.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201003/1

.