store multiple selection of list box in table field

[Irene Kral]

Hello everybody,

I hope you can help me out on this:

I have some products, which belong to more than one category. So I have my
products form, where I put a list box (multiple selection activated) to
select the category/ies the product belongs to. The categories are saved in
a different table, categories.
I wonder what I have to do, so that in the table field Category (in the
products table), there would be saved the multiple selection of my list box.

Example.

Product 1 belongs to category 1, 3, 7. So i select the first, third and
seventh choice of my list box. But now (that I activated multiple
selection), nothing is saved in the field category of the products table
(as it was saved, when I just did single selection). Instead, when I choose
a new record, selections main selected even in the new record.

Any ideas?


Thanks

Irene

 

[Irene Kral]

Thank you Steve for your prompt answer,

normalization acutally might be the answer, but:
Until now (only single selection was allowed) I actually had a list with all
the categories in my products form and whenever a new product was coming in,
I did a simple click on the category, it belonged to. Is there any way as
simple as that with the normalization you suggested?

Hope I made myself clear enough

Irene

 

[tina]

saving multiple values in one field in a record denormalizes your data,
which you don't want to do. if one product may belong to many categories,
and one category may include many products, that is a many-to-many
relationship. in Access, you model that relationship with "join" or
"linking" table, as

tblProducts
ProductID (primary key)
ProductName
<other fields that describe a product, but nothing about categories.>

tblCategories
CategoryID (pk)
CategoryName
<other fields that describe a category, but nothing about products.>

tblProductCategories
ProdCatID (pk)
ProductID (foreign key from tblProducts)
CategoryID (fk from tblCategories)
<other fields that describe a specific instance of a product related to a
category.>

the relationships are
tblProducts.ProductID 1:n tblProductCategories.ProductID
tblCategories.CategoryID 1:n tblProductCategories.CategoryID

if you're not familiar with primary keys, foreign keys, and table
relationships, recommend you STOP, and read up/more on relational design
principles before continuing to develop your database. for more information,
see http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[o;;]

Hello everybody,

I hope you can help me out on this:

I have some products, which belong to more than one category. So I have my
products form, where I put a list box (multiple selection activated) to
select the category/ies the product belongs to. The categories are saved
in a different table, categories.
I wonder what I have to do, so that in the table field Category (in the
products table), there would be saved the multiple selection of my list
box.

Example.

Product 1 belongs to category 1, 3, 7. So i select the first, third and
seventh choice of my list box. But now (that I activated multiple
selection), nothing is saved in the field category of the products table
(as it was saved, when I just did single selection). Instead, when I
choose a new record, selections main selected even in the new record.

Any ideas?


Thanks

Irene