Davisro
asked on
Multiple Not Like statements in query
Experts,
I have a data set of about 600,000 records that I've imported into Access. My requirement is to exclude records that have any words in a description field that exist in a list of 20-40 exclusion keywords. I'm thinking of managing the list of exsclusion keywords on a form connected to a table... tblKeywords. How would I write a query that would read the table values and create a query criteria such as
Select * FROM tblData
WHERE tblData.description not like 'A', or not like 'B' or not like 'C' etc.
Would I need to use VBA to rip the values from the keyword table?
Is there a limit to how many Not Like expressions i can put in a statement?
Not sure how to do this...
For the sake of clarity, if my keywords are Flowers, Funeral, Party, Birthday, Cake, etc, then i want to exclude any records that have any of these words in the description field.
So
"Retirement Party for Joe" - exclude
"Flowers for hospital visit" - exclude
"Legitimate Expense" - include
The simplest solution would to have a very long WHERE NOT LIKE expression. But, we'd like to be able to have a non technical person manage the list so hence, managing it on a form
thanks
I have a data set of about 600,000 records that I've imported into Access. My requirement is to exclude records that have any words in a description field that exist in a list of 20-40 exclusion keywords. I'm thinking of managing the list of exsclusion keywords on a form connected to a table... tblKeywords. How would I write a query that would read the table values and create a query criteria such as
Select * FROM tblData
WHERE tblData.description not like 'A', or not like 'B' or not like 'C' etc.
Would I need to use VBA to rip the values from the keyword table?
Is there a limit to how many Not Like expressions i can put in a statement?
Not sure how to do this...
For the sake of clarity, if my keywords are Flowers, Funeral, Party, Birthday, Cake, etc, then i want to exclude any records that have any of these words in the description field.
So
"Retirement Party for Joe" - exclude
"Flowers for hospital visit" - exclude
"Legitimate Expense" - include
The simplest solution would to have a very long WHERE NOT LIKE expression. But, we'd like to be able to have a non technical person manage the list so hence, managing it on a form
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
With 600,000 records you may wish to add some other filtering as well.