I have a Table "BuddyList". It contains 3 fields ID (PK), Buddy1, Buddy2. Each buddy field is a is a lookup field in Table "Employees". Buddy 1 & 2 gets the first and last names of the employees. I want to throw that into a report but on the report I need to include each person's position.
Team <---Lookup--- Team
Buddy1 <---look up from Employees
Buddy2 <---look up from Employees
Relationships Employees and BuddyList: EmployeesID to Buddy1 One-to-Many Employees and Information: EmployeesID to EmployeesID One-to-Many--Inforce Ref Integrity (checked) Cascade Update(checked) Cascade Delete (checked) Team and Employees: Team(team) to Team(employees) One-to-Many
You can use subselects to pull buddy information from the employee table if you'd like. If there is a slight chance of there ever being more then two buddies for a given employee, you should then employ the second table with a format:
You have the field EmployeesID, which is the key to reference for master detail relationships, and buddy_employeesID, which is ANOTHER reference to EmployeesID to be used for retrieving information about all the buddies for a given employee.