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,944
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,944
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,944
Joined: 4-December 03
From: Northern Virginia, USA


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

If literals can 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 Today, 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
 


Custom Search
RSSSearch   Top   Lo-Fi    16th January 2018 - 12:45 PM