I have employees that work with multiple customers of my company.
That's called a one to many relationship. It can be implemented with a separate table for customers and link from customers to employees by employee ID.
But I suspect the following is also true:
Do multiple employees work with the same customer?
If so, you have a many to many relationship and this requires an added join table to link employees to customers. Please post back with confirmation if this is the case and I can then explain how to work with many to many relationships in greater detail.
Thank you for help. Yes, I need to create this as a many to many relationship, as multiple employees will also work with one or more customers. Would also like to thank you for a post you put up over a year ago that I just used on a phone number calcualtion field. It works great! I'm very inexperienced and your post was easy to follow.
Ok, Many to many relationships are set up like this:
Employees::__pk_EmployeeID = Employee_Customer::_fk_EmployeeID
Customers::__pk_CustomerID = Employee_Customer::_fk_CustomerID
A portal to Employee_Customer on an Employees layout can list all customers to which they are linked. A Portal to the same table on a customers layout can list all employees that worked with that customer.
Data specific to that pairing of employee and Customer can be stored in fields defined in the Employee_Customer table (called a "join" table).
Reports can be set up on layouts based on the join table to list data from both Employee and Customer tables.
Here's a download link to a demo file on many to many relationships you may want to examine to see some options in how to set this up: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html