linking names with functions

[Alex]

I'm trying to link a group of names to a smaller group of functions, some of
the names will be associated with more than one function and some of the
functions will be used by more than one name.

On a form I'll have 2 combo boxes, one for the names and one for the
functions, the idea being that only the functions associated with the chosen
name will be available in the 2nd combo box.

I'll also need a seperate form with a combo box of all the names and a list
box containing all the possible functions, so that I can select which
functions should be associated.

How do I go about structuring my database so that I can do this? Currently I
have seperate tables for the names and functions, each with 2 fields, an
autonumber field and text for storing the names or functions.

Any help would be greatly appreciated.

 

[John Vinson]

I'm trying to link a group of names to a smaller group of functions, some of
the names will be associated with more than one function and some of the
functions will be used by more than one name.

On a form I'll have 2 combo boxes, one for the names and one for the
functions, the idea being that only the functions associated with the chosen
name will be available in the 2nd combo box.

I'll also need a seperate form with a combo box of all the names and a list
box containing all the possible functions, so that I can select which
functions should be associated.

How do I go about structuring my database so that I can do this? Currently I
have seperate tables for the names and functions, each with 2 fields, an
autonumber field and text for storing the names or functions.

You have a Many to Many relationship here. Your first paragraph is a
good definition of such a relationship!

Whenever you have such a relationship, you need *A THIRD TABLE* to
model it. This table should have two fields: a link to the Primary Key
of the names table, and to the Primary Key of the functions table.
These should be Long Integers to link to the autonumber fields.

You can use a Form based on one of the two tables you now have, with a
subform based on this new table. Put a Combo on the subform based on
the other "ONE" side table.


John W. Vinson[MVP]