MS Access 2007: Query to retrieve records that contains keywords stored in a table
This MSAccess tutorial explains how to set up a query to retrieve records that contain keywords stored in a table in Access 2007 (with screenshots and step-by-step instructions).
See solution in other versions of Access:
Question: In Microsoft Access 2007, I'm trying to solve a problem relating to wildcards. I have a table that contains a list of 200 keywords and another table that I want to search using those keywords.
I need to retrieve all records from the second table that contain one of those keywords anywhere in the field, not just "whole field".
How can I do this?
Answer: We'll demonstrate how to do this with the example below.
In this example, we have a table called key_words that contains a list of 200+ keywords in the keys field and a table called Part_names where we want to search for those keywords anywhere in the nomenclature field. This is equivalent to using the LIKE condition as follows:
Like '*keyword*'
To do this, we could create the following query:
The SQL for this query is:
SELECT Part_names.nomenclature, key_words.keys FROM key_words, Part_names WHERE (((InStr([Part_names].[nomenclature],[key_words].[keys]))>0));
This query uses the Instr function to check if the nomenclature field contains key anywhere in the value. If it finds key within nomenclature, it will return a value greater than 0.
Running the query above would return the following records:
Advertisements