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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|