Querying specific text from a larger body of text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that is primarily text, and want to design a query that will find certain terms in a field that consists of many more words. For example, say I have a database of friends and one of the fields is "Favorite Foods." Everyone likes more than one thing but I just want to know who likes apples. I'm fairly new to Access and have no experience with code; an example expression would be appreciated. Thank you!
 
Adam,

For this type of query, you would want to use the LIKE operator and
wildcards, for example:

Select * from MyTable where [Favorite Foods] like "*apples*"

The use of the asterisk before and after the criterion 'apples' means to
find it in any location within the field Favorite Foods.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Adam said:
I have a database that is primarily text, and want to design a query that
Click to expand...
will find certain terms in a field that consists of many more words. For
example, say I have a database of friends and one of the fields is "Favorite
Foods." Everyone likes more than one thing but I just want to know who likes
apples. I'm fairly new to Access and have no experience with code; an
example expression would be appreciated. Thank you!
 
In a query, you might set the criteria on your "Favorite Foods" field to:

Like "*apples*"

This will exclude records that don't have the text "apples" anywhere in the
"Favorite Foods" field.

However, it will not exclude records that have "applesauce" in the "Favorite
Foods" field because "applesauce" starts with "apples". If you want to
exclude records like this, and items in your "Favorite Foods" field are
space-delimited, you might set the criteria on your "Favorite Foods" field
to:

="apples" Or Like "apples *" Or Like "* apples *" Or Like "* apples"

This will exclude records that where the "Favorite Foods" field does not
satisfy at least one of the following:

1. Equals "apples",

2. Starts with "apples", followed by a space,

3. Includes "apples", preceeded and followed by a space,

4. Ends with "apples", preceeded by a space.

In the long run, you might consider normalizing your data, and storing each
of a person's favorite foods in a separate row of a separate but related
table. For example, if your table (say, it's named "Persons") looks
something like this:

Person ID, Person Name, Favorite Foods
1, "John Doe", "apples bananas plums"
2, "Mary Smith", "liver onions bacon"
3, "Jane Brown", "porkchops applesauce"

you might create a new "Favorite Foods" table (with a primary key, or at
least a unique index of Person ID and Food) that looks something like this:

Person ID, Food
1,"apples"
1,"bananas"
1,"plums"
2,"liver"
2,"onions"
2,"bacon"
3,"porkchops"
3,"applesauce"

and is related to the "Persons" table on the "Person ID" field.

You could then remove the "Favorite Foods" field from the "Persons" table.

To answer the question of, "Whose favorite foods include apples?", you might
then use a query whose SQL looks something like this:

SELECT
[Persons].*
FROM
[Persons]
INNER JOIN
[Favorite Foods]
ON
[Persons].[Person ID] = [Favorite Foods].[Person ID]
WHERE
[Favorite Foods].[Food] = "apples"

Adam said:
I have a database that is primarily text, and want to design a query that
Click to expand...
will find certain terms in a field that consists of many more words. For
example, say I have a database of friends and one of the fields is "Favorite
Foods." Everyone likes more than one thing but I just want to know who likes
apples. I'm fairly new to Access and have no experience with code; an
example expression would be appreciated. Thank you!
 
Wow, one I can answer. In design view of your query, in the Criteria field under Favorite Foods, type *apples*. The * is a wildcard character, and will tell Access to look for "apples" with any text before or after.

----- Adam wrote: ----

I have a database that is primarily text, and want to design a query that will find certain terms in a field that consists of many more words. For example, say I have a database of friends and one of the fields is "Favorite Foods." Everyone likes more than one thing but I just want to know who likes apples. I'm fairly new to Access and have no experience with code; an example expression would be appreciated. Thank you!
 
Back
Top