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.
