Connecting two tables and using yes/no efficiently

[tvsmvp]

I've got products that I'd like to associate with a topic (that is, "Widget A" is related to "Using Widgets" and "History of Widgets.") I'd like to make this association via a simple yes/no check box - and at least when creating the association, I'd like to see a large checkerboard pattern (products as column heads, topics as rows, yes/no boxes as values).

Now, from what I've read, actually creating column heads (based on my product list that could increase/decrease) is bad design. I believe I'm supposed to create a list (read: rows) of products and a list of topics. Then, I'm supposed to connect them via a table that simply contains IDs from both. Problem is, I'm having brain failure here when it comes to table layout/design. I'm ending up with a hodge-podge of queries, sub-forms, etc. Can anybody give me a little goose in the right direction? Maybe there's an example out there you might point me to?

What has worked best so far is a series of queries that eventually lead to a cross-tab query - but it seems needlessly complicated - and does nothing to solve my original issue: how do I construct the initial tables efficiently?

 

[traingamer]

one (reasonably simple) table layout:

Products
prodID
Desc
etc.

Topics
TopicID
TopicName
etc.

ProdustTopic
PTID
prodID (foreign key)
TopicID (foreign key)



Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[tvsmvp]

Thanks for the guidance - You're confirming I'm on the right track. From there I've created a third table (combining the id's) - and added a column for "Related yes/no." Am I correct in assuming that this fact, "should this topic be related to this product, yes or no" should be entered into this joining table?

 

[traingamer]

No. An entry in the ProductsTopic (misspelled earlier) table means that there is a relationship. for instance:

PTID ProdId TopicID prodname TopicName
1       	1	1	Widgets	Using Widgets
2	        1	2	Widgets	Hist of Widgets
3	        2	2	Gears	Hist of Widgets
4	        2	3	Gears	Simple Ideas
5	        3	3	levers	Simple Ideas

is a sample of what might be in the table.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[tvsmvp]

Thanks for the course correction.
Because there are hundred of topics and products, I'd like to create a query or form that would allow me to check yes or no to create the relationship. Check my thinking: First I'd create a table containing all 5000-6000 possible matchups (topics to products) with one column dedicated to a yes/no box. Then I'd create a form with a sub-query that would, in effect, squelch results, filtering on product.

Does that make sense - or is there a simpler way to do this?