okay... i've got three tables: clients, employee, jobs. jobs is the primary table. each job can only have one client but can have multiple employees. my problem, is that i seem to only be able to register only one employee name to each job. the relationship is based on the field "employee name" which, in the employee table is a calculation combining first name and last name. what do i need to do to be able to assign multiple employees to each job?

Create relation one to many between Jobs and Employee by Job_Id which you must have in the Jobs and Employee table.

If an Employee might be assigned to more than one Job, then add an Employee_Job join table, linked to Employee by EmployeeID and to Job by JobID. Then use an Employee_Job portal on either an Employee layout or a Job layout for adding Employee_Job instances.

Hi,

There can be one more mechanism to achieve this...

In Jobs table create one more field say employee_Id.. and store all related employee_ids separated by carriage return in that field.

Regards,

MetaSys

Hi,

There can be one more mechanism to achieve this...

In Jobs table create one more field say employee_Id.. and store all related employee_ids separated by carriage return in that field.

This multi-line key will work if you don't need to track job assignment dates or other details about each job assignment. If you do need to remember things about job assignments, then stick with the join table.