Topic Select (1 Viewer)

This is a general query about the structure of my database, albeit a fairly simple one!

I have records which hold a name, date and then a selection of various topics. I need to be able to run a query to select a topic and then to report on what name and date contains that topic.

I've set up a table full of YES/NO fields, one for each topic, but can see all kinds of problems when I come to write queries, ie having to write a query for every topic

Any suggestions would be appreciated.

I'm going to make an assumption:

Your records look like this:

myKeyWordSearchTable
--myName
--myDate
--myListOfTopicKeywordsRunTogetherInOneFieldIfImLuckySeparatedByComms

So a record might look like

"Journal of American Philatelists", "25-Dec-2002", "Hapenny 1892 Black, Borneo 1925 Black, Scams, Tongue Disease"

If you are entering data like this, and you have the choice to do it, you chose to do it wrong. If you get this data from another source, you need to write a parser before actually storing it.

In the ideal world, each entry should be a date, a name, and a SINGLE topic keyword. Makes the table deeper but narrower. If you do it that way, it suddenly makes sense to put an index (non-unique, of course) on the keyword field. Which will help your search performance tremendously.

OK, having now issued the appropriate advice on what it SHOULD look like, here is how to make it work with the run-together stuff.

When searching for the keywords, use the following...

... "WHERE [stKeyWords] LIKE ""*" & [stTargetedText] & "*"""

When you let the quotes get evaluated, the result would be

WHERE [stKeyWords] LIKE "*your-search-text*"

tblMain
MainID (Primary key & autonumber)
Name
Date
Topic

tblTopics
TopicsID (Primary key & autonumber)
MainID (Foreign key and long integer)
TopicName

You do not need the Yes/No field.

Create a Main form based on tblMain. Create a datasheet form based on tblTopics and make it a subform on the Main form. Select a name in the Main form and add Topics in the subform that apply to the selected record.

A simple query will allow you to put in the topic(s) you want and all records in the main table that meet that criteria will be returned.

hth,
Jack

Thank You

A big thanks to Jack for his advice, the dbase is now under construction!!