linking a cell to multiple cells in another table

[Timothy Reza]

I am trying to find a way to link single cells from on table to multiple
cells in another table.

I have a table with Names as one field, and other data in multiple other
fields.
I would like to be able to link the Blah field (or User John) to multiple
Access Groups.
I would then Like to link user Tim to multiple access groups from the same
list of Access Groups, but to a different amount of group items.


I.e: John has subgroup (Group 1, Group 2, Group 3)
Tim has subgroup (Group 1, Group 3)
etc.

Name Location Blah
-John 1 x
-Tim 2 y
-Carl 3 z
-etc 99 q

I have another Table with "Access Groups"

Access Group Description
Group 1 Access Group 1
Group 2 Access Group 2
Group 3 Access Group 3

....please help

--Tim

 

[Douglas J. Steele]

What you have is a classic many-to-many relationship (one user may be
associated with multiple groups, one group may be associated with multiple
users). What's done to resolve that many-to-many is introduce a third
intersection entity that contains the keys of the two other tables.

Take a look in the Northwind database that comes with Access. This is
analogous to the relationship between Products and Orders (one product may
be on many orders, one order can contain many products). The Order Details
table resolves that many-to-many.

In terms of how to maintain the information, the usual approach is to have a
form based on one of the two original tables, and a subform based on the
intersection entity (usually with a combo box on the subform that's linked
to the other of the two original tables). Take a look at the Orders form in
Northwind (which uses the Orders Subform as a subform) for an example.