Running Reports - Access 2003

[Andell]

Hello,

I'm entering information in a database of photographs that was set up by
someone else. Now, I want to run a report (or is it a query?) that picks out
certain information. Each entry has fields such as: photo no.; title;
description; keywords; and the photo itself. I want to be able to run a
report that extracts all the photos that have boat (for example) as a
keyword. I want the corresponding photo for each entry to appear in the
report next to the other information (i.e. number, description) I have
indicated.

 

[Allen Browne]

The best way to do this would be to create a related table for the keywords.

Each photo can have multiple keywords, so you use a one-to-many
relationships between photos and keywords. Hence it's a related table, with
field like this:
PhotoID relates to the primary key of the Photo table
Keyword the word
Then on your Photo form, you have a subform in Continuous Form view, so the
user can add multiple rows if there are multiple keywords. The table would
be named something like PhotoKeyword.

If you want set keywords (rather than just being able to type any old
keyword), you would also have a Keyword table listing all the valid choices.
The Keyword field in the PhotoKeyword would then relate to this table, and
you could use a combo for choosing the word.

Once you have that set up, your question becomes, "How can I filter my form
so it shows only the photos that have (say) 'boat' as a keyword?" And here's
the answer to that question:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

That's the best solution for your question. It is correctly normalized, very
efficient (will respond in less than a second, even if you have tens of
thousands of photos), and accurate (completely reliable matching.)

If you don't want to go that way, it would be possible to stick all the
words in the one Keywords field, and then filter using the Like operator
with wildcards, e.g.:
Me.Filter = "[Keywords] Like ""*" & Me.txtWot2Find & "*"""
Me.FilterOn = True
That will be much less efficient, and less reliable. For example, if you
filter for 'rat', you get photos that have that anywhere in the Keywords
field, so those that contain rate, liberate, and so on.