Multiple catagories per product

[Gregg Knapp]

Using Access 2003.
DB currently has a single products table with relationships to a
number of supporting tables, including a categories table. A
request was made recently to allow for each product in the db
to be linked to multiple categories, if necessary - For query
related reasons.

I have a few ideas about how this could be done, but I'm
wondering if anyone out there has done something similar and
has already come up w/ an efficient way to link these. I'm sure
it's going to require the creation of another table.

Ideas?
Thanks for the input!

Gregg Knapp

 

[Allen Browne]

Category table:
CategoryID primary key
CategoryName

Product table:
ProductID primary key
ProductName
...

ProductCategory table:
ProductID foreign key to Product.ProductID
CategoryID foreign key to Category.CategoryID

Enter as many record in the ProductCategory table as needed.

On your Product form, include a subform for the categories the products
belong to. The subform will be bound to ProductCateogry, and will have a
combo box where the user selects the categories, one per row in the subform.

 

[Gregg Knapp]

Perfect. Thanks for the tip!

Gregg

Category table:
CategoryID primary key
CategoryName

Product table:
ProductID primary key
ProductName
...

ProductCategory table:
ProductID foreign key to Product.ProductID
CategoryID foreign key to Category.CategoryID

Enter as many record in the ProductCategory table as needed.

On your Product form, include a subform for the categories the products
belong to. The subform will be bound to ProductCateogry, and will have a
combo box where the user selects the categories, one per row in the subform.