Join Error 2

[telnet1]

Hi,


I have the following tables:

Activity_tb
Employee_tb

The Activity_tb has the following fields:

Activity-id
Action
Employee_rep
Employee_mrg

The Employee_tb has the following fields:

Employee_Key
Name
level

I am trying to create a one-to-many relationship from the Employee_tb, the Employee_Key(pk) to both fileds Employee_rep and Employeemrg.

Access lets me create the first relationship between the to tables using Employee_mrg and Employee_key. When I try to create another relationship between the same tables but a different fields. error their is already arelationship created, do you want to edit the relationship.

can I not create more then one realtionship between tables using diffrent fields?

 

[rafe]

i generally only do this in queries (not in the db relationship) but if you want to you should be able to this.

i'd actually recommend against doing this because you can create pointer chaos in the db if you don't get the relationships correct. you wind up not seeing records that you KNOW are there or some such nonsense.

but... hey, we're all adults here & i've been wrong before & a query may not be the answer for you

what you need to do is show one of the tables twice (or more) probobly the employee_tb table.

so when you select the tables make the primary relationship one to many. on the second relationship on you're going to need to set up the relationship to show all of your Activity_tb records and only those in employee_tb that match.

in this case i'd also steer clear of enforcing referential integrity on the secondary relationship

 

[JerryDennison]

Rafe is giving you good advice. You should not enforce referential integrity unless the data in the child table is totally dependent on a record in the parent table. While you could make the argument that the one cannot exist without the other this is fundamentally not true. You could have someone not in your employee table that is the rep or mgr(i.e. someone that hasn't been hired yet or maybe a temporary or outside consultant?). Using the Employee table as a lookup, or recursive, table is a good idea. But it isn't necessary that they be linked in the relationship view to give you the desired results(i.e. reduced "housekeeping", better control over choices, and continuity of spelling, etc.).