Couting the occurance of keywords in a memo field

I have a table which contains a memo field that holds a series of keywords seperated by a space, eg "egg apple orange", and I need a query that will count how many times a keyword appears in the records.

The database is a store of personal details and the field in question contains disability information of those involved. The query will be used to report how many of those with details stored are affected by different disabilities, but because one person can have multiple disabilities those fields with more than one are not included in the totals properly.

Can anyone help me?

I'm not sure if the soundex algorithm will help with this at all. But from what you've described the structure of your database is lacking normalisation.

I think the keywords of disabilities should be stored in a seperate table linked to each child rather than having to find them from a memo field. So what you would have is a table with a list of different disabilities, then have a junction table that will store the ID of each disability linked to the child's ID. From that you would be able to count how many of those keywords in the junction table are present per child.

With your current structure, the only way I can see this possible is to:

1. Use the Split() function to split the contents of the memo using space as the delimeter.
2. Put the list of keywords in an array.
3. For each item in the returned array (from the Split) you would check against your list of keywords (in the array)
3. Return a count based on step 3.

The returned count will be an aliased field that is referencing a function which performs the above.

Welcome to AWF

As vbaInet says, you need them in their own table.

A simple way I use to count word in memo fields is

[abc] is the memo field

Exp1: Replace([abc],"Zurich","") This replaces the word "Zurich" with no characters. Thus the sentence....One of the largest insurance companies in the world is Zurich, a company based in Switzerland...will become.......One of the largest insurance companies in the world is , a company based in Switzerland

Exp2: Len([abc]) Counts all the characters in the record

Exp3: Len([Exp1]) Counts all the characters if the word is replaced with no characters.

You can group the last two as one expresion

Exp4: (Len([abc])-Len([Exp1]))/6 The "6" being the number of characters in "Zurich" and the answer is the number of times Zurich is in the record.