Multiple 1-to-Many?

[OxDavis]

Ok I have a table of clients, and each client will have 2 employees of differing types assigned to them. I had hoped to combine all employees into one table, assign them differnet titles in the employee table by way of combo box and then have the appropriate fields in the client table only show employees of that corresponding type.

The problem is I can create one 1-to-many relationship from the primary key of the employee table, but I cannot create 2 and have the queries work properly.

I suppose I could break the tables apart, but the table has other uses that require it to be combined. Any suggestions, workarounds?

 

[cuttsy]

Your problem is not totally clear to me but it doesn't sound like there should be any problem.

 

[OxDavis]

Let me try and explain another way:
I want to create a 1-to-many relationship between 1 field in one table and 2 fields in another. Access will not let me Enforce Referential Integrity on this relationship. Is there any way around that?

 

[cuttsy]

So in one of the tables your Primary Key is a composite key.

 

[Pat Hartman]

You are not creating the relationship properly. The "client" doesn't contain two "employee" IDs, each "employee" contains one "client" ID. The relationship is always from the child to the parent, never from the parent to the child.

 

[OxDavis]

Hmm, that makes sense, I will make the proper adjustments.

Thanks for your help!