A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SQL "NOT IN" doesn't function as expected (refresh)



 
 
Thread Tools Display Modes
  #1  
Old March 1st, 2010, 11:01 AM posted to microsoft.public.access.queries
Farkyss
external usenet poster
 
Posts: 25
Default SQL "NOT IN" doesn't function as expected (refresh)

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.
  #2  
Old March 1st, 2010, 03:33 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default SQL "NOT IN" doesn't function as expected (refresh)

So using TRIM on both Serial_Number fields does not work. THe following fails

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 Trim(pd.SERIAL_NUMBER) NOT IN
(SELECT Trim(SERIAL_NUMBER)
FROM INSTALL WHERE
SERIAL_NUMBER IS NOT NULL);

As a check have you tried something like the following to make sure the record
in the INSTALL table actually contains the value 999789.
SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER = '999789'

An alternative 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)
LEFT JOIN INSTALL as I
ON Pd.SERIAL_Number = I.Serial_Number
WHERE I.SerialNumber Is Null

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

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.

  #3  
Old March 1st, 2010, 04:13 PM posted to microsoft.public.access.queries
orange via AccessMonster.com
external usenet poster
 
Posts: 15
Default SQL "NOT IN" doesn't function as expected (refresh)

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

  #4  
Old March 1st, 2010, 04:44 PM posted to microsoft.public.access.queries
Farkyss
external usenet poster
 
Posts: 25
Default SQL "NOT IN" doesn't function as expected (refresh)

SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

produces expected results whereas

SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

procudes an unexpected, empty result. FYI, the lookup for this field in the
INSTALL is

SELECT [PORD].[PORD_ID], PORD.[SERIAL_NUMBER] FROM PORD ORDER BY
[SERIAL_NUMBER];

have also tried replacing that with this
SELECT PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER];
as the original lookup was made by access's 'insert a field from another
table' wizard
This produces the same incorrect results

"John Spencer" wrote:

So using TRIM on both Serial_Number fields does not work. THe following fails

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 Trim(pd.SERIAL_NUMBER) NOT IN
(SELECT Trim(SERIAL_NUMBER)
FROM INSTALL WHERE
SERIAL_NUMBER IS NOT NULL);

As a check have you tried something like the following to make sure the record
in the INSTALL table actually contains the value 999789.
SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER = '999789'

An alternative 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)
LEFT JOIN INSTALL as I
ON Pd.SERIAL_Number = I.Serial_Number
WHERE I.SerialNumber Is Null

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

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.

.

  #5  
Old March 1st, 2010, 05:43 PM posted to microsoft.public.access.queries
orange via AccessMonster.com
external usenet poster
 
Posts: 15
Default SQL "NOT IN" doesn't function as expected (refresh)

Farkyss wrote:
SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

produces expected results whereas

SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

procudes an unexpected, empty result. FYI, the lookup for this field in the
INSTALL is

SELECT [PORD].[PORD_ID], PORD.[SERIAL_NUMBER] FROM PORD ORDER BY
[SERIAL_NUMBER];

have also tried replacing that with this
SELECT PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER];
as the original lookup was made by access's 'insert a field from another
table' wizard
This produces the same incorrect results

So using TRIM on both Serial_Number fields does not work. THe following fails

[quoted text clipped - 77 lines]
project is frozen until this is fixed.

.

Farkyss,

I was having problems with some of the queries then I realized I had a typing
error. I was using 999789 in Install and 999879 in PORD. I'm using Acc 2003
on XP SP3.
Once I adjusted that an now am getting expected results using John Spencer's
query (orig)
Here are images from my tests:

My test files http://www.box.net/files#/files/0/f/.../1/f_397107264
John's Query SQL
http://www.box.net/files#/files/0/f/.../1/f_397107274
Result of John's query
http://www.box.net/files#/files/0/f/.../1/f_397107256

Also, I am able to run successfully
SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

and
SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

--
Message posted via http://www.accessmonster.com

  #6  
Old March 1st, 2010, 06:06 PM posted to microsoft.public.access.queries
Farkyss
external usenet poster
 
Posts: 25
Default SQL "NOT IN" doesn't function as expected (refresh)

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

.

  #7  
Old March 1st, 2010, 06:33 PM posted to microsoft.public.access.queries
Farkyss
external usenet poster
 
Posts: 25
Default SQL "NOT IN" doesn't function as expected (refresh)

Did your values in the INSTALL table come from PORD via a lookup table?
That's the issue as I see it.

"orange via AccessMonster.com" wrote:

Farkyss wrote:
SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

produces expected results whereas

SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

procudes an unexpected, empty result. FYI, the lookup for this field in the
INSTALL is

SELECT [PORD].[PORD_ID], PORD.[SERIAL_NUMBER] FROM PORD ORDER BY
[SERIAL_NUMBER];

have also tried replacing that with this
SELECT PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER];
as the original lookup was made by access's 'insert a field from another
table' wizard
This produces the same incorrect results

So using TRIM on both Serial_Number fields does not work. THe following fails

[quoted text clipped - 77 lines]
project is frozen until this is fixed.
.

Farkyss,

I was having problems with some of the queries then I realized I had a typing
error. I was using 999789 in Install and 999879 in PORD. I'm using Acc 2003
on XP SP3.
Once I adjusted that an now am getting expected results using John Spencer's
query (orig)
Here are images from my tests:

My test files http://www.box.net/files#/files/0/f/.../1/f_397107264
John's Query SQL
http://www.box.net/files#/files/0/f/.../1/f_397107274
Result of John's query
http://www.box.net/files#/files/0/f/.../1/f_397107256

Also, I am able to run successfully
SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

and
SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

--
Message posted via http://www.accessmonster.com

.

  #8  
Old March 1st, 2010, 06:44 PM posted to microsoft.public.access.queries
Farkyss
external usenet poster
 
Posts: 25
Default SQL "NOT IN" doesn't function as expected (refresh)

This alternative query doesn't work either. Believe it's because Access isn't
processing the values of SERIAL_NUMBER in the INSTALL table correctly since
they're looked-up

An alternative 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)
LEFT JOIN INSTALL as I
ON Pd.SERIAL_Number = I.Serial_Number
WHERE I.SerialNumber Is Null


  #9  
Old March 1st, 2010, 10:39 PM posted to microsoft.public.access.queries
orange via AccessMonster.com
external usenet poster
 
Posts: 15
Default SQL "NOT IN" doesn't function as expected (refresh)

Farkyss wrote:
Did your values in the INSTALL table come from PORD via a lookup table?
That's the issue as I see it.

SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

[quoted text clipped - 39 lines]
and
SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

No, I simply typed them in. That's part of why I suggested some table
structures or a sample database to help with solution.

--
Message posted via http://www.accessmonster.com

  #10  
Old March 2nd, 2010, 02:59 PM posted to microsoft.public.access.queries
Farkyss
external usenet poster
 
Posts: 25
Default SQL "NOT IN" doesn't function as expected (refresh)

You can download the db with some junk data in from he
http://www.clanimperium.net/testdb.accdb

"orange via AccessMonster.com" wrote:

Farkyss wrote:
Did your values in the INSTALL table come from PORD via a lookup table?
That's the issue as I see it.

SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

[quoted text clipped - 39 lines]
and
SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

No, I simply typed them in. That's part of why I suggested some table
structures or a sample database to help with solution.

--
Message posted via http://www.accessmonster.com

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.