More than one category

[Mary Ann]

I have a list of customers, and in a separate table a list of categories. I
understand how to create a one-to-many relationship and a lookup. What do I
do if the customer can belong to more than one category?
I have created “many-to-many†relationships before e.g. between a courses
table and a staff table, with the junction table in the middle to put people
on course, effectively creating two one-to-many relationships.
However, my need for the customers is much smaller. I just want to be able
to show they belong to two categories and be able to enter this in the form
with their other address details etc.

 

[Allen Browne]

The junction table will be the way to do.

Ultimately, you will have:
- a little table with one record for each category.
- a junction table,with fields:
o CustomerID
o CategoryID

The junction table will be a little subform (Continuous view) on your
Customer form. The subform will have a combo for selecting the category (one
per row.)