Retrieving records with a form.

[Lukeskywelder]

I am a welding trainer. I have a file cabinet of Welding Journal articles
with folders of different welding categories. I have Access 2003 and I wish
to make a database of welding magazine articles for research purposes. The
table’s fields are: Date-Page (primary key), Category, Article Title, Key
Word 1, Key Word 2, Synopsis. Besides the table, it has a form that enters
the info for each record. The form works great for entering the data for the
title, key words, synopsis…etc. However, I also want to have a search form
that will have text boxes for entering search words, hit a button and have
records that include the search words in any of the fields show up in the
bottom of the form for review so I can spot articles that address the welding
issues I am resolving.

The Access Help and other research papers presents a head-spinning array of
suggestions that do not seem to nail down what I really want to do. I do not
know to base a form on a query, filter or report. If a query what type, a
crosstab, or what? I’m asking this here in general questions because I do not
know which newsgroup of Access database to center on. Thank you for your help.

 

[Sharkbyte]

First off, I would suggest you break your KeyWords into their own table.
Like this:

tblArticles
Date-Page (PK)
Category
ArticleTitle
Synopsis

tblKeyWords
Date-Page (PK) (FK)
KeyWord (PK)

This will allow you to only need to do a single search to find related
articles.

As for searching, you might consider a ComboBox of KeyWords. That would
eliminate user spelling errors, and would be an initial indicator of what
type of articles are available.

Create a form, with a ComboBox, with the RowSource based on the tblKeyWords
table. Then you can use a Forms Wizard to create a Continuous Form based on
the tblArticles table. Add the form, the wizard created, as a subform to the
one you created.

HTH

Sharkbyte

 

[Lukeskywelder]

Thanks for your input. The advice gives me some things to consider for either
this database or future database projects.

I see a need to add a few more details. I have a 2nd table besides the
Article Data table. The other table is called Categories and has 2 fields: id
# (auto number/PK) and the category names. It reflects the physical filing
cabinet folders that are in welding categories, e.g. “Aluminumâ€, “Brazingâ€,
“GMAW (mig welding)â€â€¦etc. The Data Entry form has a ComboBox that has a drop
down list that has its RowSource as that Categories table and stamps the
selected category name in the cell of the Article Data Category Field. The
categories involved in welding are limited (I have only have 23 for our
training purposes) and are very repeatable. So, a drop-down ComboBox works
well for that Data Entry form

However, key words for searching are almost infinite and often combined with
an infinite number of other key words. For example, I may need to search for
all articles that discuss “hydrogen embrittlement†in the “HAZ†(Heat
Affected Zone). Or all articles that discuss “hydrogen embrittlement†and
“electrodes†that minimize that type of embrittlement. So what I am in a
quandary over is: How do I do one or two key word searches in a database? By
a filter, report or a query, and if so, what kind of query? Also, how may I
make a form that can pass on such key words to a filter, report or query and
then display the records of all articles that employ those key words? Any
suggestions of a direction I can go? Thanks again for your help.