Query Part of Text Field

[Guest]

I'm sure this is something ridiculous I'm overlooking, but I can't find
reference to it in any of our Access support books, nor on various websites.

I've got a sort of HR/Contact database built, very basic with all the info
in one data set. One field, titled COMPETENCIES, is set up to allow users
to list their various skills and competencies in a text field. For example,
one employee has "MS PowerPoint, Logistics, Knowledge Management, BPR"
populated in their COMPETENCIES field.

I'm trying to create a query that will allow users to search all of the
contents of this set across all entered employees for specific terms. I.e.,
searching "PowerPoint" would retrieve the record cited above, as well as any
others with the same competency listed.

Of course, it's not working the way I've set it up with the brackets in the
Criteria option of the query design. How can I set up a query to retrieve
entered keywords from text fields with extraneous data? Any help is MUCH
appreciated.

 

[Tom Ellison]

Dear Cheshire:

It is not difficult to look for "Logistics" within the string. Use a
criteria:

WHERE YourColumn Like "*Logistics*"

and the query will find it just fine.

However, a note about compentencies like "MS PowerPoint" and others.

The field is text, and will almost certainly NOT be consistent. One person
will omit "MS" on the front. No problem if you just search for
"PowerPoint". Another person will put in "Power Point" (note the space!).
Now you have a problem. You would need to find all the various ways people
misspell or alter such names for your search to be complete.

If there are certain compentencies you expect may be or interest later on,
it would be wise to create a list of these in a separate table. On the form
(whether on paper or the screen) you can show this list and have respondents
check the ones applicable. This translates into a totally consistent
internal form that can be queried consistently, quickly, and without such
errors.

Just something to think about. It's the reason database professionals get
the big bucks (ha, ha) for their experience in knowing such pitfalls in
advance of creating and filling in a database. Getting it right to start
is, as MasterCard says, priceless.

Tom Ellison

 

[Guest]

Thank you, Tom. Glad to hear all things are still possible.

One bit I forgot to mention -- the search term would need to be entered by
the query user, not something that's predefined within the query design.
I've tried using the Like [insert search term here] function, but it's just
not catching... is that doable, or am I wishing at blinked out stars here?

 

[Tom Ellison]

Dear Cheshire:

As a parameter or from a control, the point is to end up with a string the
same as if you had put it into the criterion directly. So, append an
asterisk before and after.

If from a control on your form:

"*" & [Forms]![FormName]![ControlName] & "*"

As a parameter, similarly:

"*" & [Enter Parameter] & "*"

Tom Ellison

Thank you, Tom. Glad to hear all things are still possible.

One bit I forgot to mention -- the search term would need to be entered by
the query user, not something that's predefined within the query design.
I've tried using the Like [insert search term here] function, but it's
just
not catching... is that doable, or am I wishing at blinked out stars
here?

Dear Cheshire:

It is not difficult to look for "Logistics" within the string. Use a
criteria:

WHERE YourColumn Like "*Logistics*"

and the query will find it just fine.

However, a note about compentencies like "MS PowerPoint" and others.

The field is text, and will almost certainly NOT be consistent. One
person
will omit "MS" on the front. No problem if you just search for
"PowerPoint". Another person will put in "Power Point" (note the
space!).
Now you have a problem. You would need to find all the various ways
people
misspell or alter such names for your search to be complete.

If there are certain compentencies you expect may be or interest later
on,
it would be wise to create a list of these in a separate table. On the
form
(whether on paper or the screen) you can show this list and have
respondents
check the ones applicable. This translates into a totally consistent
internal form that can be queried consistently, quickly, and without such
errors.

Just something to think about. It's the reason database professionals
get
the big bucks (ha, ha) for their experience in knowing such pitfalls in
advance of creating and filling in a database. Getting it right to start
is, as MasterCard says, priceless.

Tom Ellison

 

[John Vinson]

the search term would need to be entered by
the query user, not something that's predefined within the query design.

Tom's right. You can't have it *both* completely free form, *and*
useful for reliable retrieval.

In order to *reliably* get data back out of a database, you must have
some control over what goes into the database. Garbage in... garbage
out.

If you're not concerned about hitting all the keywords, or about
missing some candidates because they used keywords a bit differently
than you expect, your design will work. But to reliably get the data
in and out, a better design would be to use THREE tables - Candidates,
Keywords, and CandidateKeywords; the candidate could pick keywords
from a defined list using a Combo Box, and add them (one record at a
time) to a Subform based on the CandidateKeywords table. You could
even set the combo box's Limit to List property to "No" so that if the
candidate wishes to claim competency in "Fly fishing" or "Astral
Travel" or some other skill you didn't include, they could add it to
the table - but at least the set of competencies that you have
predefined would be entered consistantly.

John W. Vinson[MVP]

 

[Tom Ellison]

Yes, John,

I had actually in mind a design I've used, in which the person could check
pre-defined skills and also free type other skills not in the list. This
could possibly be the best of both worlds. Those that are pre-defined would
be definitively and unambiguoulsy selected. Still, you would have a place
to represent other things not pre-defined. In fact, I even built an
interface to allow new items to be moved from the free-type list to new
items inserted in the pre-defined list.

For maximum power and flexibility, this is about the best I could come up
with.

(The topic wasn't "skills" as discussed here, but there is a large range of
things that can be handled like this.)

Tom Ellison