Query which filters the database

[acces]

Hello!



I would like to make an query for my database that filters and removes entires which contains one of the words from a list of words. What is the best way to do this?

Should i make a second database with the words i want to remove and link it with the main database someway? In that case what is the best way to do this?

Or should i make an big criteria that contains all these words?

The database has about 200k entries and there is about 2k different words i want to filter the database from.

Thank you
Br

[nick404]

You could do a delete query and select the field containing the word(s) you want to delete as the criterion. It would take a long time.

I do not know if you can split it with specific criteria like that...

[ranman256]

A second table, (not database) that has the keywords.
Yes with this table of keywords you can run a query to delete * from table where [word] IN table...

A delete query MUST use the IN clause. You cannot join it to the keyword table.
(create a FIND DUPLICATES query using the query wizard, then design it, this will show you how to use the IN clause. Its a query IN a query)

[acces]

Thank you for your reply!


I am sorry if i was a bit unclear I mean that i have a table that i need to filter from serchwords in another table.


Is it possible to make a SQL Query something like these


Select Field1
From Table1
Where (Field does not contain a word from the Field2 int table2)


I know you can make SQL Queries that exclude entries that are exact matches from another table. But is it possible to exclude on search words from another table? Example: If i have an entry "mike" in Field customer in table1 and i have the word "%ik%" in the searchword field in table 2. Can i then make a sql code that excludes mike because mike has "ik" in it?


If this is not doable in SQL what language should i choose in Access? VB, Dynamic SQL or the criteria functions?

[June7]

If Table2 has the actual string 'ik' as value in Field2 then a query can do pattern match, if not, no and need VBA - very complicated VBA.

If Field1 has 'Mike' and Field2 has bike, trike, Mike - how will query know to match on '*ik*'? And if it could, it would also match to bike and trike.