I have a question regarding access and one-to-many relations:
I want to built up a relationship between two tables. One table contains different information and personal contact detail of (in this case) consultants and the other one about trainees. The main idea is that each consultant can be the attachment figure for many trainees.
I have introduced as foreign key in my consultant table and as primary key in the trainee table the "trainee_id". If I try to build a relationship between these two fields, access sees automatically my primary key as the one side and my foreign key as the manyside. Unfortunately, that is not what I wanted: it would namely mean that each trainee has many consultant as attachment figure.
Anybody knows, how i can change my tables in order to build the relationship which gives me the information I really need (each consultant can be the attachment figure for many trainees)? I tried to change also my primary key in the trainee table, but if I make a relationship between the two "trainee_id" (none is a primary key), I cannot enforce the referential integrity and obviously at the same time I cannot see on the line nether the "1" nor the "infinity".