3 Replies Latest reply on Feb 28, 2013 6:20 PM by GreggMccombs

    Table Relationship

    GreggMccombs

      Title

      Table Relationship

      Post

           Should be an easy one - I have 2 tables, a customer table and and employee table.  The customer table is related to the employee table through the employee id number, a unique number assigned to each employee.  When I am in the customer table, in input the employee number and it populates employee information associated with that customer.  My need is to have a 2nd employee, in certain situations, that has aidid with that customer.  How can I enter a 2nd employee id number in the customer table and get the 2nd employee name and associated data?  Thank you.

        • 1. Re: Table Relationship
          philmodjunk

               You could use two fields in Customer to hold two IDs from Employee. Each could link to a different occurrence of the Employee table.

               Or you can set it up as a full many to many relationship with a join table between customer and ID. This second option would allow you to link any number of employees to any number of different customers.

               Option1:

               Employees 1-------<Customers>------Employees 2

               Customers::_fkEmployeeID1 = Employees 1::__pkEmployeeID
               Customers::_fkEmployeeID2 = Employees 2::__pkEmployeeID

               Option 2:

               Customers-----<Employee_Customer>-------Employees

               Customers::__pkCustomerID = Employee_Customer::_fkCustomerID
               Employees::__pkEmployeeId = Employee_Customer::_fkEmployeeID

               With option 2, you'l place a portal to Employee_Customer on the Customers layout to link a customer to different employees.

               For an explanation of the notation used in this post, see the first post of this thread: Common Forum Relationship and Field Notations Explained

          • 2. Re: Table Relationship
            GreggMccombs

                 Thank you - option 1 is what I have been trying to get to work.  I have an employee id in Employee table (ID 1), then I have a 2nd employee ID (ID 2)which is a calculation = to employee ID 1.  Are you suggesting that I do not need a 2nd ID number in the employee table, but use a 2nd ID number in the customer table, then create a duplicate table in employees?  Sorry if this seems simple.  Thanks.

            • 3. Re: Table Relationship
              GreggMccombs

                   Thank you - I have resolved the issue.  Your links to notation led to another link and I solved it.  Very much appreciated!