Can I select multiple values for one field?

B

buckeyeNo3

I have a list of 150 publications. For each publication, I need to associate
it with an area of my company, a "domain". In some cases, there are several
different domains that apply to one publication. How do I set up my tables
to make this happen? Is there a way to pick several values for one field?
 
D

Douglas J Steele

While it's possible to store more than one value in a field, it's strongly
discouraged, as it's actually a violation of database normalization
principles.

What you've got is a classic many-to-many relationship: each publication can
belong to one or more domains, each domain can apply to one or more
publications. The standard way to handle this is to introduce a third
"intersection" table to handle the relationships. This third table will
contain fields corresponding to the primary keys of the other two tables.
All of those fields together will be the primary key of the third table.
 
W

Wayne Morgan

What you are looking for is called a many-to-many relationship. You actually
have 2 tables with a 3rd table to link them.
 
A

Allen Browne

You have a classic many-to-many relation here:
One department can order many publications;
One publication can go to many departments.

You need 3 tables:
- Department (one record for each department, with a DepartmentID primary
key);
- Publication (one record for each publication, with a PublicationID primary
key);
- DepartmentPublication: one record for every combination

The 3rd table will have fields:
DepartmentID which department wants this publication;
PublicationID which publication it is that this department wants.

You will then create a main form bound to Department, with a subform bound
to the DepartmentPublication table. The subform will contain a combo box
that has Publication as its RowSource. The user can then select as many
publications as they want for the department in the main form--one per row.

For another example of this junction table approach, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html
 
Top