Puzzeled Design Issue

[Michael B.]

This is a beginning designer's question that is probably
so obvious that on I would be stumped by it. Here is the
question.

What is the best way to add multiple categories to a
single record from a another table? I basically want to
build an input form that will have a drop down list with
categories that the user can make multiple selections on
and add new categories to when adding a record. Would the
category selections go into a single cell and if so how
would this be setup?

Thanks so much for any of your perspectives. I'm sure
there must be a number of different ways to do this.

Michael B.

 

[John Vinson]

This is a beginning designer's question that is probably
so obvious that on I would be stumped by it. Here is the
question.

What is the best way to add multiple categories to a
single record from a another table?

Well, the best way is not to do it that way AT ALL. If you have a Many
to Many relationship between items and categories, you need to
implement it *as a many to many relationship* - with a third table
storing the category selections.

You'ld have three tables: your current table; a table of Categories
listing all the valid choices; and a CategoryAssignment table related
one-to-many to both other tables. It would have a foreign key field
linked to your table's Primary Key, and a CategoryID field linked to
the primary key of the Categories table.

To enter data, you can use a Form based on your table, with a subform
based on the CategoryAssignment table. On this subform you'ld have a
Combo Box based on the Categories table.

 

[Michael B.]

Yes, that does make some sense. I understand that at
least two tables would be used, ie. a table for the
(primary records) and a look-up table for the
(categories). What I am trying to emulate is the Master
Categories function in MS Outlook in a Contact Database.

Your answer doesn't suggest whether, 'order_details'
would contain multiple entries going into a single record
cell or some other configuration. Can you explain a
little further?

mb

 

[Guest]

Ah Ha! I knew that it wasn't as simple as I wanted it to
be. Thanks for the explanation and suggestion. I'm not
sure that I understand it completely but it gives me
enough food for thought that I will probably be able to
make something out of it.

Your help is most appreciated.

Michael B.