Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-17-2003, 08:07 AM   #1
Raven
Soozie's watchin' you!
 
Raven's Avatar
 
Join Date: Apr 2002
Location: United Kingdom
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Raven
Topic Select

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.

Raven is offline   Reply With Quote
Old 01-17-2003, 01:33 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,250
Thanks: 86
Thanked 1,624 Times in 1,507 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
I'm going to make an assumption:

Your records look like this:

myKeyWordSearchTable
--myName
--myDate
--myListOfTopicKeywordsRunTogetherInOneFieldIfImLuck ySeparatedByComms

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*"
The_Doc_Man is offline   Reply With Quote
Old 01-17-2003, 01:35 PM   #3
Jack Cowley
Registered User
 
Join Date: Aug 2000
Location: San Francisco
Posts: 2,639
Thanks: 0
Thanked 2 Times in 2 Posts
Jack Cowley is on a distinguished road
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

Jack Cowley is offline   Reply With Quote
Old 01-23-2003, 03:02 AM   #4
Raven
Soozie's watchin' you!
 
Raven's Avatar
 
Join Date: Apr 2002
Location: United Kingdom
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Raven
Thank You

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

Raven is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 09:01 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World