By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,691 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

adding multiple categories to a table with products...

P: n/a
I need help with a tricky questions which I belive have a simple
solution? For those who know Nwind.mdb, the example database that
comes with microsoft access, it has several different tables. One of
them is categories. What I need to do is somehow make it possible for
a product to be listed in several categories. However, I do not know
how to do this. Does anyone have a clue??? Any help is appreciated!
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On 27 Jan 2004 10:45:23 -0800, er**@freestyletravel.no (Erik Thorsen) wrote:
I need help with a tricky questions which I belive have a simple
solution? For those who know Nwind.mdb, the example database that
comes with microsoft access, it has several different tables. One of
them is categories. What I need to do is somehow make it possible for
a product to be listed in several categories. However, I do not know
how to do this. Does anyone have a clue??? Any help is appreciated!


What you are talking about is called a Many-to-Many relationship (M-M). Each
product can have several categories, and each category can belong to several
products.

The way an M-M relationship is modeled is by making a 3rd table called a
junction table. Generally, the junctino table has a compound primary key
including fields that are foreign keys to each of the other 2 tables.

tblProducts
--------
* ProductID
...

tblCategories
----------
* CategoryID
...

tblProductCategory
------------------
* ProductID
* CategoryID

Now, from each of the original tables, create a 1-M relationship to
tblProductCategory on the matching field names. Each record in
tblProductCategory now represents a valid association of a product to a
catagory.

To model this in the user interface, create a Product form with a subform for
tblProductCategory, and use ProductID as the Master/Child link field. Make a
combo box in the subform for CategoryID that looks up from tblCategories.
Now, each row in the subform indicates a category for the product showing in
the master form, and you can add them just by selecting from the combo in the
blank new row space at the bottom of the subform or delete them by deleting
rows from the subform.
Nov 12 '05 #2

P: n/a
Hello Steve!!

Thanks a lot. This has helped me a great way on the road to success!!!
However, I still have one problem. When I create the subform and add the
combo box, I use the standard wizard helping me to make everything
correct. Here is what I do:
I choose that I want the combo box to look up values from a table.
I choose the table Categories
I add the field cName ( for category name, in other words, a category
like Husky Tour)
Then I choose to hide column key
Finally I choose remember value for later use.
I use cName for label on the combo box.
When I do this I am able to select Husky Tour from my list but no new
combo box pops up upon selection. So it seems I can only choose one
option.....
Any ideas on how to fix this??? You have already saved me 10-20 hours
and I really appreciate any help you or anyone else can give me! Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
On 28 Jan 2004 21:49:09 GMT, Erik Thorsen <er**@freestyletravel.no> wrote:
Hello Steve!!

Thanks a lot. This has helped me a great way on the road to success!!!
However, I still have one problem. When I create the subform and add the
combo box, I use the standard wizard helping me to make everything
correct. Here is what I do:
I choose that I want the combo box to look up values from a table.
I choose the table Categories
I add the field cName ( for category name, in other words, a category
like Husky Tour)
Then I choose to hide column key
Finally I choose remember value for later use.
I use cName for label on the combo box.
When I do this I am able to select Husky Tour from my list but no new
combo box pops up upon selection. So it seems I can only choose one
option.....
Any ideas on how to fix this??? You have already saved me 10-20 hours
and I really appreciate any help you or anyone else can give me! Thanks!


Is your subform set to Continuous?
Nov 12 '05 #4

P: n/a
Yes, my subform is set to continous. So I really don't know what is
wrong with it. Are there any other settings I should take into
consideration? I am sorry about bugging you with these probably too easy
questions but I can't figure it out even though I have looked and
searched a lot of places!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.