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
  #11  
Old March 3rd, 2010, 01:18 AM posted to microsoft.public.access.queries
Farkyss
external usenet poster
 
Posts: 25
Default SQL "NOT IN" doesn't function as expected (refresh)

Correct behaviour can be restored by changing the SERIAL_NUMBER in the
INSTALL table to be a plain text box rather than a LOOKUP table of values
from the PORD table. Upon changing, the values stored in the SERIAL_NUMBER
field suddenly change to some unrelated digits.

Since this behaviour is clearly incorrect, could someone with a MVP tag
please report it as a bug? SQL queries on a field should be returning the
field's value, not a reference number.

As for my db? I'll have to go with a textbox. It isn't the functionality it
SHOULD have been, but it'll do

"Farkyss" wrote:

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

.

  #12  
Old March 3rd, 2010, 03:03 AM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default SQL "NOT IN" doesn't function as expected (refresh)

Farkyss wrote:
Correct behaviour can be restored by changing the SERIAL_NUMBER in the
INSTALL table to be a plain text box rather than a LOOKUP table of values
from the PORD table. Upon changing, the values stored in the SERIAL_NUMBER
field suddenly change to some unrelated digits.

Since this behaviour is clearly incorrect, could someone with a MVP tag
please report it as a bug? SQL queries on a field should be returning the
field's value, not a reference number.

As for my db? I'll have to go with a textbox. It isn't the functionality it
SHOULD have been, but it'll do


Sounds like the problem is the lookup junk in your table. Another reason to
avoid them.

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

  #13  
Old March 3rd, 2010, 11:37 AM posted to microsoft.public.access.queries
Farkyss
external usenet poster
 
Posts: 25
Default SQL "NOT IN" doesn't function as expected (refresh)

The 'lookup junk' was put in by Access itself with it's wizard. The
implementation of the form is -supposed- to be independant of the underlying
storage, otherwise it makes a mockery of the SQL standard. I will be making
effort in the future to completely avoid Access - it's a load of trash

"PieterLinden via AccessMonster.com" wrote:

Farkyss wrote:
Correct behaviour can be restored by changing the SERIAL_NUMBER in the
INSTALL table to be a plain text box rather than a LOOKUP table of values
from the PORD table. Upon changing, the values stored in the SERIAL_NUMBER
field suddenly change to some unrelated digits.

Since this behaviour is clearly incorrect, could someone with a MVP tag
please report it as a bug? SQL queries on a field should be returning the
field's value, not a reference number.

As for my db? I'll have to go with a textbox. It isn't the functionality it
SHOULD have been, but it'll do


Sounds like the problem is the lookup junk in your table. Another reason to
avoid them.

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

.

  #14  
Old March 3rd, 2010, 03:03 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)

Sorry you feel that way. The unfortunate fact is that what is displayed in
datasheet view of a query is not what is stored if you are using a field that
uses the lookup feature. That's why most of us avoid using the lookup field.

On forms you can use a combobox to display one value while actually storing
another value. So, I can have a combobox that stores a primary key from my
personnel table in a foreign key field of another table that lists family
members. That is proper use of a relational database.

The simple way to solve the problem would be to change your subquery so it
references the table that really contains the value you are looking for.
WIthout more detail, I would say the sub-query might look something like the
following.

SELECT T.TheSerialNumberField
FROM INSTALL INNER JOIN TheSourceTableForTheLookup as T
ON INSTALL.Serial_Number = T.SomeRelatedField

I was going to attempt to download your sample db, but for some reason I got a
404 error.

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

Farkyss wrote:
The 'lookup junk' was put in by Access itself with it's wizard. The
implementation of the form is -supposed- to be independant of the underlying
storage, otherwise it makes a mockery of the SQL standard. I will be making
effort in the future to completely avoid Access - it's a load of trash

"PieterLinden via AccessMonster.com" wrote:

Farkyss wrote:
Correct behaviour can be restored by changing the SERIAL_NUMBER in the
INSTALL table to be a plain text box rather than a LOOKUP table of values
from the PORD table. Upon changing, the values stored in the SERIAL_NUMBER
field suddenly change to some unrelated digits.

Since this behaviour is clearly incorrect, could someone with a MVP tag
please report it as a bug? SQL queries on a field should be returning the
field's value, not a reference number.

As for my db? I'll have to go with a textbox. It isn't the functionality it
SHOULD have been, but it'll do

Sounds like the problem is the lookup junk in your table. Another reason to
avoid them.

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

.

  #15  
Old March 3rd, 2010, 08:36 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default SQL "NOT IN" doesn't function as expected (refresh)

That is not a bug. In fact, I explicitly asked you the question, the 25th of
February, and you answered it was NOT a look-up value, but JUST a value
being forced to be in another table:

==================
In the INSTALL table, the serial_number field was a "field from another
table" which means it is restricted to values of serial_number from a PART
table. It isn't a look-up in any other sense, it's just a field with
restricted values it can take.
==================

But clearly, it is a look-up value, and the information then given was quite
appropriate, if you would have identified that it was a look-up value:

================
if you have


Table1
serialNumber lookup value
1010 joe
1011 joe


Table2
serialNumber lookup value
1011 joe


then, if you use the un-lookup value, 1010, it is not in table2, and you
get, trough lookup, that joe is not in table2 !

Be sure that the lookup value is not leading you in the wrong conclusion.
Jet always process its comparisons/tests on the native, not -lookup, values.
================






Vanderghast, Access MVP


 




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 04:03 PM.


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