Step-by-step, Keyword search

[Guest]

I am very new to Access and am having trouble finding instructions that I can
understand for the action I want to perform. I need to add an option in my
database for a user to search the entire database by keyword. From what I
can tell, a parameter query is the best method for this, but when I try to
follow the steps in Help, I am getting various errors.

Can anyone just give me simple, step-by-step instructions? I simply want my
user to be able to enter a word and have Access pull up a list of records
that include that word. It would also be nice if the user could pull up a
specific record from this list, but I'm not sure that can even be done.

Any help anyone can offer would be greatly appreciated...

Thanks!!

 

[fredg]

I am very new to Access and am having trouble finding instructions that I can
understand for the action I want to perform. I need to add an option in my
database for a user to search the entire database by keyword. From what I
can tell, a parameter query is the best method for this, but when I try to
follow the steps in Help, I am getting various errors.

Can anyone just give me simple, step-by-step instructions? I simply want my
user to be able to enter a word and have Access pull up a list of records
that include that word. It would also be nice if the user could pull up a
specific record from this list, but I'm not sure that can even be done.

Any help anyone can offer would be greatly appreciated...

Thanks!!

Create a parameter query that includes the field you wish to search
in.
As criteria on that field, write:
Like "*" & [Enter the keyword] & "*"

Run the query. Enter the keyword when prompted and all records that
contain that word anywhere in that field will be returned.

 

[John Vinson]

I am very new to Access and am having trouble finding instructions that I can
understand for the action I want to perform. I need to add an option in my
database for a user to search the entire database by keyword. From what I
can tell, a parameter query is the best method for this, but when I try to
follow the steps in Help, I am getting various errors.

Can anyone just give me simple, step-by-step instructions? I simply want my
user to be able to enter a word and have Access pull up a list of records
that include that word. It would also be nice if the user could pull up a
specific record from this list, but I'm not sure that can even be done.

Any help anyone can offer would be greatly appreciated...

Thanks!!

Well, let's step back a bit.

A "Database" in Access jargon means a .MDB (or .MDE or other) database
file, a container for multiple Forms, Reports, Queries, Tables, and
other objects. You can't really "search a database". You can search in
a Table using queries.

Secondly, a properly-designed table has each field containing data
about a different "attribute" of the Entity represented by the table.
For instance, a mailing list table would have a field for FirstName,
another field for LastName, a third field for Address, and so on.
Generally you'll be searching ONE field for values appropriate to that
field - if you're searching an address-list database for people named
Caldwell, you wouldn't want people named Smith who happen to live in
Caldwell, Idaho turn up.

If your table has a Field named "Keyword" containing (say)

Database; Relational; Access; Keyword; Search

then it's a bit of a problem - typically a field should be "atomic",
containing only one word. If that's what you do have then you can
create a new Query based on your table. Select whatever fields you
want the user to see; on the criteria line under this Keyword field
type

LIKE "*" & [Enter keyword:] & "*"

When the user opens the Query they'll be prompted "Enter keyword:" (or
whatever you put in the brackets); when they type something Access
will search for what they type as a substring of the field (so if they
type "Sea" looking for references to the ocean they'll find SEArch...
be careful!)

Could you describe the structure of your tables, how you have keywords
entered, and what user interface you would ideally like to have?

John W. Vinson[MVP]

 

[Guest]

Thanks for responding, I appreciate the help.

My Access database was created to track original and current locations of
PowerPoint presentations. We've moved all of our PPTs to a new folder on our
network and want people to be able to find them quickly if they need to. I
set it up with a form to enter one record for each moved presentation, info
includes the filename, Presentation name, a brief description, the date
created and the author. I then created a query to weed out any duplicates
and several reports so that people could easily track down what they needed.

Up until now, I have been searching the database by opening my "find
duplicates" query and using the Find and Replace feature to find any records
containing the word or number that I enter. This works fine for me, but now
that we are getting ready to roll out to the rest of the department, I was
hoping there was an easy way to create a button they could click on, be
prompted to enter the word/number they are trying to find, then bring up a
list of records that contain that word/number.

Because I've just been using find and replace, I haven't entered any
keywords up to this point.

Any suggestions?

I am very new to Access and am having trouble finding instructions that I can
understand for the action I want to perform. I need to add an option in my
database for a user to search the entire database by keyword. From what I
can tell, a parameter query is the best method for this, but when I try to
follow the steps in Help, I am getting various errors.

Can anyone just give me simple, step-by-step instructions? I simply want my
user to be able to enter a word and have Access pull up a list of records
that include that word. It would also be nice if the user could pull up a
specific record from this list, but I'm not sure that can even be done.

Any help anyone can offer would be greatly appreciated...

Thanks!!

Well, let's step back a bit.

A "Database" in Access jargon means a .MDB (or .MDE or other) database
file, a container for multiple Forms, Reports, Queries, Tables, and
other objects. You can't really "search a database". You can search in
a Table using queries.

Secondly, a properly-designed table has each field containing data
about a different "attribute" of the Entity represented by the table.
For instance, a mailing list table would have a field for FirstName,
another field for LastName, a third field for Address, and so on.
Generally you'll be searching ONE field for values appropriate to that
field - if you're searching an address-list database for people named
Caldwell, you wouldn't want people named Smith who happen to live in
Caldwell, Idaho turn up.

If your table has a Field named "Keyword" containing (say)

Database; Relational; Access; Keyword; Search

then it's a bit of a problem - typically a field should be "atomic",
containing only one word. If that's what you do have then you can
create a new Query based on your table. Select whatever fields you
want the user to see; on the criteria line under this Keyword field
type

LIKE "*" & [Enter keyword:] & "*"

When the user opens the Query they'll be prompted "Enter keyword:" (or
whatever you put in the brackets); when they type something Access
will search for what they type as a substring of the field (so if they
type "Sea" looking for references to the ocean they'll find SEArch...
be careful!)

Could you describe the structure of your tables, how you have keywords
entered, and what user interface you would ideally like to have?

John W. Vinson[MVP]

 

[John Vinson]

Up until now, I have been searching the database by opening my "find
duplicates" query and using the Find and Replace feature to find any records
containing the word or number that I enter. This works fine for me, but now
that we are getting ready to roll out to the rest of the department, I was
hoping there was an easy way to create a button they could click on, be
prompted to enter the word/number they are trying to find, then bring up a
list of records that contain that word/number.

Contain that word/number.... where? What field in the table?

You can create a Query based on the table. Put a criterion on whatever
field you want searched:

LIKE "*" & [Enter search term:] & "*"

If the user opens the Query they will be prompted; the query will then
display all the records containing that term in the selected field.


John W. Vinson[MVP]

 

[Guest]

Contain that word/number.... where? What field in the table?

Anywhere, in any field. I want to be able to search the entire record for
the word that is entered. I want to be prompted to enter my keyword, then
have Access search the ENTIRE record for that word -- so if my word appear in
the Presentation title, the filename, the description or even the author's
name field, I want it to be called up. Is there any way to search multiple
fields or just the entire table for the
words that are entered by the user?

Thanks again for all of your help. As a new Access user I'm sure my
questions seem silly but I'm not sure how else I can find the answers I need.
Sure appreciate you sticking with me while I try to figure all of this out.

Up until now, I have been searching the database by opening my "find
duplicates" query and using the Find and Replace feature to find any records
containing the word or number that I enter. This works fine for me, but now
that we are getting ready to roll out to the rest of the department, I was
hoping there was an easy way to create a button they could click on, be
prompted to enter the word/number they are trying to find, then bring up a
list of records that contain that word/number.

Contain that word/number.... where? What field in the table?

You can create a Query based on the table. Put a criterion on whatever
field you want searched:

LIKE "*" & [Enter search term:] & "*"

If the user opens the Query they will be prompted; the query will then
display all the records containing that term in the selected field.


John W. Vinson[MVP]

 

[John Vinson]

Anywhere, in any field. I want to be able to search the entire record for
the word that is entered. I want to be prompted to enter my keyword, then
have Access search the ENTIRE record for that word -- so if my word appear in
the Presentation title, the filename, the description or even the author's
name field, I want it to be called up.

Well... this seems a Very Odd Thing To Do. Ordinarily, each field
contains a different TYPE of information - and if you want to search
for the author's name, you'ld search the author's name field for it;
if you want to search for a filename, you'ld search the filename
field. Have you tried using the built-in Query By Form feature, which
will let you do just this?

Is there any way to search multiple
fields or just the entire table for the
words that are entered by the user?

Yes. You can put the criterion I suggested

LIKE "*" & [Enter keyword:] & "*"

under *every one* of the fields you want searched; put each criterion
on a separate line in the query grid, to use "OR" logic, returning the
record if the keyword appears in any one of the fields.


John W. Vinson[MVP]

 

[Albert D.Kallal]

Just open a form, and hit ctrl-f