Proper multi-category structuri

[Raphael T]

Hi all

I'd like advice on the following issue:
I have a "Categories" table with fields (*CatID, CatName), and in my
"Items" table I need to have the necessary fields some that each
record can be associated to any number of categories. Of course, I'd
like to be able to retrieve the records corresponding to one category
afterwards.
The only way I have found is to have the CatID's of the categories
associated with each record stored in a text field with some
separator. My "Items" table would then look like this (*ItemID,
ItemName, ItemCat) with ItemCat filled in like "CatID1; CatID2;
CatID3". But besides the fact I don't like that multiple-value field
practice, it's so annoying to create an efficient app with such a
design since it multiplied the number of queries, and the queries are
quite complex.
So I was wondering whether anyone would have a better idea.

Any help will be appreciated.
Thanks in advance
RT.

 

[Duane Hookom]

Don't store multiple values in a single field. If a single item can relate
to many categories, you should create a junction table:

tblItemCats
=================
ItemID
CatID

If an item belongs in three categories, you would represent this in three
records.