UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Not In A Set Of Values Failing, Access 2007    
 
   
mmadani
post Dec 19 2017, 10:20 PM
Post#1



Posts: 323
Joined: 27-June 08



Hello Everyone;

I have a query that I am trying to limit the results based on values. I am using the NOT IN ('Value1', 'Value3', Etc.....)

However It is not working.

Not IN ('E250','EEI','E240','EEK','EE2','EEY','E237','E235','EEX','E230','EEA','EXE','EXW','E220','EED','E210','EEE','EZE','E147','EEZ','E140','EEC','E130','EEM'.'E120','EEG','E110','EEU','EFX','WFF','E100','W110','WWT','W120','WWV','W130','WWA','WPX','P210','WPB','WW2','WXM','W210','WWW','W220','WWH')

I still get E140, E120 etc...

What I am I doing wrong

When I copy the SQL from the Query this is what I get below:

SELECT DISTINCT Format([record_date],"mm-dd-yy") AS Shortdate, SPR_Log.id, SPR_Log.name, SPR_Log.substate, SPR_Log.CR_reference, SPR_Log.sub_system, SPR_Log.StationID, SPR_Log.CR_category, SPR_Log.CR_type, SPR_Log.headline, SPR_Log.severity, SPR_Log.submitter, SPR_Log.description, SPR_Log.ID1, SPR_Log.implementer, SPR_Log.implementation, SPR_Log.validation_description, SPR_Log.impacted_items, SPR_Log.proposed_change, SPR_Log.product, SPR_Log.submitter_date, SPR_Log.Closure_date, SPR_Log.record_date, IIf([record_date]="",Null,DateDiff("d",CDate(Left([record_date],InStr([record_date],"GMT")-1)),Date())) AS Age, IIf([Age] Between 0 And 29," Less than 30 days",IIf([Age] Between 30 And 60,"30 - 60 days",IIf([Age] Between 60 And 90,"60-90 days",IIf(IsNull([Age]),"No Age",IIf([Age]>90,"Over 90 days"))))) AS bucket, SPR_Log.Primary_Block, SPR_Log.Secondary_Block, SPR_Log.submitter_CR_reference, SPR_Log.Comments, SPR_Log.component
FROM SPR_Log
WHERE (((SPR_Log.State) Not In ('closed','Complete')) AND ((SPR_Log.StationID) Not Like "*E120*"))
ORDER BY SPR_Log.id;


The Criteria in the query is not showing up !!!!

Thanks for your time !

Mike pullhair.gif
Go to the top of the page
 
mmadani
post Dec 19 2017, 10:54 PM
Post#2



Posts: 323
Joined: 27-June 08



I got it figured out.

I had a dot instead of a comma in a couple of places

Solved

Thanks All
Go to the top of the page
 
RJD
post Dec 19 2017, 11:38 PM
Post#3


UtterAccess VIP
Posts: 8,487
Joined: 25-October 10
From: Gulf South USA


Hi Mike. I see you've got that sorted, but when I see all those Not In criteria, I just have to ask ... is there not a shorter way to express this? What are you looking for rather than Not looking for?

Or even a subquery Not In (SELECT StationID FROM ... ). Just curious ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
JonSmith
post Dec 20 2017, 03:38 AM
Post#4



Posts: 3,576
Joined: 19-October 10



To expand on Joe's train of thought.
NOT IN has poor performance compared to IN from my recollection.
Go to the top of the page
 
GroverParkGeorge
post Dec 20 2017, 08:46 AM
Post#5


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


I'm not 100% sure of the performance problem, although I try to avoid IN() and Not In() .

There's an even more significant problem with that string, though. Let me step aside and relate the story of a phone call last week that lead to a billable hour of my time thumbup.gif

Client has a complex reporting system for its production of ice cream. So, different reports call for filters that include/exclude certain "classes" of products. A set of reports that has worked for years failed to include a newly added product to their line of delicious frozen treats. The original developer is looonnnggg gone and they had no idea what the problem could be. It took 45 minutes for me to go into a remote web session and track through a nested set of queries, one at a time, until the culprit popped out: Class in ("E3", "F4",D2") Fixing the problem was simple after that: Class in ("E3", "F4",D2", "R1") But, then we had to track down OTHER reports with OTHER nested queries where the same criteria had been applied. To wrap up a long-winded explanation, hard-coding values into any kind of criteria is bad juju unless you can find no other way to get there. And that's almost never going to be the case, IMO.

This client is racing to implement a new, improved, much more robust and fancy reporting system, so they'll not pay to have anything done with this messy situation for now. Still, I expect another call soon as they start running additional month and year end reports.....

--------------------
Go to the top of the page
 
Jeff B.
post Dec 20 2017, 09:11 AM
Post#6


UtterAccess VIP
Posts: 10,065
Joined: 30-April 10
From: Pacific NorthWet


If there are soooo many values it could not be (i.e., Not In()), what about the idea of creating a table with one row per NOT value. That way, if the list should ever grow, shrink or change, a simple entry in the table fixes the routine(s) that use the "not in" list.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
mmadani
post Dec 24 2017, 09:47 PM
Post#7



Posts: 323
Joined: 27-June 08



Hello Friends;


The desired values are in (value1, value2, etc..)

The Not in is a huge list , so if they are in , we are good, if looking for every other record that does not meet the IN criteria, then that gives me the exceptions

Hope I explained myself clearly

Thanks for all of your help gents

Merry Xmas fundrink.gif

Mike
This post has been edited by mmadani: Dec 24 2017, 09:49 PM
Go to the top of the page
 
mmadani
post Dec 24 2017, 09:59 PM
Post#8



Posts: 323
Joined: 27-June 08



Hello Joe;

I have stations that are located on an East -West Platform (roughly 26 of them Aprox)

WHen I report on conditions of the east west, I apply certain action queries if the location is part of the east west.

IF not I need to apply a different summation or calculation if not part of the East west. So I tried to do this quickly for the lack of time.

So NOT IN East west locations, means it is in North-South Locations thus a different calculation

So count problems in east west where location is in (location 1, location 2, Etc)

If not in East west count location and it will be in North South.

I would love to see an example of solving this using a table as indicated here.

OF course I could attempt the find text in description that you helped me with, and create a table of E-W locations ,and Table for north south. , But don't know the syntax to do so hence my using the IN and NOT IN

Thanks
Mike
This post has been edited by mmadani: Dec 24 2017, 10:00 PM
Go to the top of the page
 
mmadani
post Dec 24 2017, 10:06 PM
Post#9



Posts: 323
Joined: 27-June 08



Hi;
How do I set a query to check existence of values in a table , if I created a table for NOT IN

Please give me an Example (Code)

Thanks

Mike
Go to the top of the page
 
RJD
post Dec 24 2017, 10:37 PM
Post#10


UtterAccess VIP
Posts: 8,487
Joined: 25-October 10
From: Gulf South USA


Hi Mike: Since your Location codes all start with either "E" or "W" or "N" or "S", you already have a direction code to zero in on.

If you are dealing with a criteria in a query and you want the E and W set ...

Field ... Direction: Left([Location],1)
Criteria ... "E" or "W"

or same approach for "N" or "S".

If dealing in a query with summing a value in a totals query ...

EWCount: IIf(Left([Location],1)="E" or Left([Location],1)="W",1,0)
and set the grouping to Sum. This will give you the count of that group.

If you wanted to sum something like cost, then ...

EWAmount: IIf(Left([Location],1)="E" or Left([Location],1)="W",[Amount],0)
and set the grouping to Sum. This will give you the sum of that group.

If Dealing with VBA ...

If Left([Location],1)="E" or Left([Location],1)="W" Then
Do something
Else
Do something else
End If

Or even more completely, add before the Else ...

Else If Left([Location],1)="N" or Left([Location],1)="S" Then
do something

etc...

Or in SQL, you could use the IN or NOT IN approach ...

Left([Location],1) IN ("E","W")

Is this what you are looking for? It would surely avoid the long and difficult-to-maintain In and Not In statements you previously cited.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Jeff B.
post Dec 25 2017, 09:23 AM
Post#11


UtterAccess VIP
Posts: 10,065
Joined: 30-April 10
From: Pacific NorthWet


<Mike>

No code needed, if I recall correctly.

Create a query with your main table and your "not in" table. Join the field in question from your main to your "not in", and use IsNull for the criterion. Select any other fields you need from the main table.

If I'm recalling correctly, you'll only get records from the main table that are, well, not in the "not in" table.

(folks, feel free to jump in and correct any under-caffeinated mistakes...)

Good luck!

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
mmadani
post Dec 25 2017, 10:59 AM
Post#12



Posts: 323
Joined: 27-June 08



Hello Jeff , Joe, George ;

Not sure How I would Join them since they have nothing in common (In Table, and NOT In Table) if I understood you correctly.

Not also ( 'WPX','P210','WPB' ) are locations on the East - West, thus my dilemma . I can't select E for East and W for west exclusively .as the first character .

So I listed all locations for east west, and everything else that is not in this set , must be in the north south theoretically, but this is not solid though incase of an error in entry somewhere or NO entry in the locationID which is what in above or in the NOT IN set .


IF I got this wrong please provide me a visual example of your suggestion.

Many thanks

Mike
This post has been edited by mmadani: Dec 25 2017, 11:03 AM
Go to the top of the page
 
mmadani
post Dec 25 2017, 11:01 AM
Post#13



Posts: 323
Joined: 27-June 08



Joe; See my reply in the thread

Mike
Go to the top of the page
 
GroverParkGeorge
post Dec 25 2017, 11:23 AM
Post#14


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


Sometimes we get so focused on one thing that we can't see something else.

If you can confirm that the only four values in the initial position would be E, W, N or S, then there is, in fact, a fairly simple solution.

WHERE [Location] Not Like "E*" AND [Location] Not Like "W*"

That excludes everything starting with E and everything starting with W, regardless of how many locations are actually present and what they are. No longer hard-coding a list that is fragile, as I noted in my previous comment.

I think performance might not be the best with that type of comparison, but it's definitely more robust.

And, to implement Jeff's solution, once again, thinking beyond the current situation would be the way to do it. Do both tables contain a field of Locations, or do they not? If not, what specific fields are in those tables?

--------------------
Go to the top of the page
 
mmadani
post Dec 25 2017, 11:37 AM
Post#15



Posts: 323
Joined: 27-June 08



Merry Christmas George ;

""If you can confirm that the only four values in the initial position would be E, W, N or S, then there is, in fact, a fairly simple solution.

WHERE [Location] Not Like "E*" AND [Location] Not Like "W*"

That is one of the issues.. Example LocationID "F210" is in the NORTH -SOUTH , so looking for N or S will not work since 'F' is the starting character. So I can manually create a table and assign a letter to the location based on its geographical presence . Example F210 = N (for north) then filtering on all N's will give me all of the North locations, Etc..

The East West had the least amount of locations thus me using the Not in (East west) will give me everything else. I am still looking for a smarter more efficient way to do this.


Thanks for your time.

Mike
Go to the top of the page
 
GroverParkGeorge
post Dec 25 2017, 12:16 PM
Post#16


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


Okay, so the short answer is that there are several variations? And that includes "F", "N", "E", "S" and "W". Any others?

--------------------
Go to the top of the page
 
RJD
post Dec 25 2017, 12:31 PM
Post#17


UtterAccess VIP
Posts: 8,487
Joined: 25-October 10
From: Gulf South USA


Hi Mike: You have a table Station_Location_and_NO that lists the station location codes in field Location_ID. Could you simply add another field called, say, Direction, and put E or N in that field to indicate direction? There don't seem to be so many of these records that doing this should be easy. Then, when you need to differentiate, just join that table to the log by location and you have the differentiator readily available.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mmadani
post Dec 25 2017, 01:23 PM
Post#18



Posts: 323
Joined: 27-June 08



10-4 Joe. I am updating that table.

Thanks

Mike
This post has been edited by mmadani: Dec 25 2017, 01:23 PM
Go to the top of the page
 
mmadani
post Dec 25 2017, 01:31 PM
Post#19



Posts: 323
Joined: 27-June 08



Yes George there are... However not too many may be 2 to 4 Tops.

Can be added manually

Mike
This post has been edited by mmadani: Dec 25 2017, 01:32 PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 08:28 PM