All of the literature I read has said that many-to-many relationships do not work in Microsoft Access. Why do they not work? Does Microsoft Access give you an error or something? I understand what has to be done to split up the table into two one-to-many relationships.

A many to many relationship is a logical relationship. When you build tables in MS Access, or any database, you are building a physical relationship. To do this, you need to build an intersect table. It's always a many to one with the intersect table for both tables it's connecting. In the logical relationship, you can assume the intereset table exists, so you can delete it from logical discussions.

Access does not have an EXPLICIT Many-To-Many relationship. However, using One-To-Many relationships, you can create a Many-To-Many relationship.

My apologies to Colinc but, knowlegeable as he may be, I have to (amiably) disagree with him. There are times when a Many-To-Many relationship is preferable, if not absolutely necessary.

A simplified example is a situation where a company has many employees and many different tasks. Each employee is assigned multiple tasks, and each task is staffed by different groups of employees. That can be represented in a Many-To-Many relationship by:

EMPLOYEE==>EMPLOYEE_TASK<==TASK (with referential integrity enforced)

where EMPLOYEE==>EMPLOYEE_TASK is a One-To-Many relationship and TASK==>EMPLOYEE_TASK is a One-To-Many relationship.

When key fields are defined judiciously, the relationships are simple to create -- and easy to understand (easy to work with, too).

§