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
> Tighten Up SQL In Query, Access 2010    
 
   
bakersburg9
post Jan 12 2018, 05:12 PM
Post#1



Posts: 4,991
Joined: 2-November 04
From: Downey, CA


I'm writing a query to use as a make-table query, and I'm trying to streamline unwanted records - I have several "not like" this, and "Not like" that segments, and I have two questions:

Question 1, the obvious one - can I streamline it?

Question 2: is there a way to create a table with these values I don't want in my table that's created from the make-table query ?

CODE
SELECT SAPS_PR_PO.*
FROM SAPS_PR_PO
WHERE (((SAPS_PR_PO.GLID)="54111-LA North") AND ((SAPS_PR_PO.Title_SAPS) Not Like "*Boots*" And (SAPS_PR_PO.Title_SAPS) Not Like "*Penalty*" And (SAPS_PR_PO.Title_SAPS) Not Like "*Janitorial*" And (SAPS_PR_PO.Title_SAPS) Not Like "*Office Supply Order*" And (SAPS_PR_PO.Title_SAPS) Not Like "*tech ops office Supplies*" And (SAPS_PR_PO.Title_SAPS) Not Like "*luis cam*" And (SAPS_PR_PO.Title_SAPS) Not Like "*Office Supplies*" And (SAPS_PR_PO.Title_SAPS) Not Like "*AcmeSolutions*" And (SAPS_PR_PO.Title_SAPS) Not Like "*Task0877841FranciscoSoftware*"));


Any help would be greatly appreciated....
Go to the top of the page
 
datAdrenaline
post Jan 12 2018, 05:53 PM
Post#2


UtterAccess Editor
Posts: 17,949
Joined: 4-December 03
From: Northern Virginia, USA


Do you really need all the wildcards? ... I mean do you have values in SAPS_PR_PO.Title_SAPS that contain the text pattern "Task0877841FranciscoSoftware"?

---

Just wondering if literals can be used instead, at least in some cases.

--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
datAdrenaline
post Jan 12 2018, 06:00 PM
Post#3


UtterAccess Editor
Posts: 17,949
Joined: 4-December 03
From: Northern Virginia, USA


If literals can be applied .. then you can create a Table object (tblExcludedTitles) with one field ...

Exluded_Title_SAPS (Text, 255) (Primary Key)

Then add values to that table ...

Boots
Penalty
Janitorial
Office Supply Order


Then you can create what is often termed a "Frustrated Left Join" ...

SELECT SAPS_PR_PO.*
FROM SAPS_PR_PO
LEFT JOIN
tblExcludedTitles ON SAPS_PR_PO.Title_SAPS = tblExcludedTitles.Exluded_Title_SAPS
WHERE tblExcludedTitles.Exluded_Title_SAPS IS NULL

--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
datAdrenaline
post Jan 12 2018, 06:07 PM
Post#4


UtterAccess Editor
Posts: 17,949
Joined: 4-December 03
From: Northern Virginia, USA


If you want to go the "fuzzy" route ...

If literals are not to be applied .. then you can create a Table object (tblExcludeFuzzyTitles) with one field ...

Exluded_Pattern (Text, 255) (Primary Key)

Then add values to that table ...

Boots
Penalty
Janitorial
Office Supply Order


Then you can create an NOT EXISTS test ...

SELECT SAPS_PR_PO.*
FROM SAPS_PR_PO
WHERE NOT EXISTS (SELECT 1 FROM tblExcludeFuzzyTitles WHERE SAPS_PR_PO.Title_SAPS Like "*" & tblExcludeFuzzyTitles.Exluded_Pattern & "*")


(untested -- smile.gif )

--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
bakersburg9
post Yesterday, 12:30 PM
Post#5



Posts: 4,991
Joined: 2-November 04
From: Downey, CA


How do people like you KNOWthis stuff ??? I did what you said, ran it, was ready to do some trouble-shooting, and almost fell out of my chair when I clicked 'run,' and it worked the first time ! Thanks !!! This is AWESOME !!!

Thanks again !!! cool.gif

Steve
Go to the top of the page
 
datAdrenaline
post Yesterday, 03:21 PM
Post#6


UtterAccess Editor
Posts: 17,949
Joined: 4-December 03
From: Northern Virginia, USA


Awesome! ... Glad to have helped out!

---

How do we know this stuff? --- Because we once questioned someone ... and almost fell out of our chairs when we clicked 'run,' and it worked the first time! ... thumbup.gif

Then ... study WHY it works!

--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
Jeff B.
post Yesterday, 04:27 PM
Post#7


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


"How do people ... know this stuff?"

Most of us chalk it up to a mis-spent youth...

--------------------
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
 
datAdrenaline
post Yesterday, 05:37 PM
Post#8


UtterAccess Editor
Posts: 17,949
Joined: 4-December 03
From: Northern Virginia, USA


Why limit it to just youth ... smile.gif

--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th January 2018 - 06:08 AM