Database design for multiple employees on one job

[rcrooks]

Hi there!

I am designing a database that will essentially keep track of all of our companies jobs. Now, the way the old excel one works is you have for instance:

Job Number
Contract manager
Engineer
Senior Engineer

Basically multiple employees working on a job. These are entered each time a new job is created. Massive potential for wrong data being input...

The way I initially went about designing it was to create a table for all of the companies employees, another table for all the departments. You can create an employee and assign them to a department via a dropdown fed from the department table. When you create a job, you would fill in the contract manager, engineer and senior engineer form fields (via a drop down box feed from the employee table).

Now, I believe I have hit a problem. Each employee has an email assigned to them in the employee table. Now, when search through the jobs in a form, I need to display the contract manager, engineer and senior engineer's name, and their email address. The forms data source is from the MASTER table (job detail). I have tried a query for this, using a relationship between MASTER and EMPLOYEE, but because their is multiple fields linking to multiple employees, it doesn't seem to work. Name is the easy one as this is a field in the job table. Email though is stored in the employee table.

I cannot figure out how to pull through the employees email for the contract manager, engineer and senior engineer.

Do I need to create another table here containing each employees job assignment and query the job number against this table, rather than storing them on the master job table?

Current tables

MASTER (job list)


Job Number (primary key, unique number)
Contract Manager (drop down referencing the EMPLOYEE name built from a query)
Engineer (drop down referencing the EMPLOYEE name built from a query)
Senior Engineer (drop down referencing the EMPLOYEE name built from a query)

EMPLOYEE
Name (Manually typed in via form)
Department (drop down populated from query of DEPARTMENT table)
Email (manually typed in via form)

DEPARTMENT
Name (Manually typed in via form)

I hope i'm making sense here, and i've provided enough information...

And thanks for any help you can provide!

[CJ_London]

hopefully you are not using name as your real field name - it is a reserved word and using it can cause problems - change to EmpName, DeptName or similar to avoid issues.

You have a potential issue by linking on name - what happens if you have two employees with the same name? or one is female, gets married and changes their name?

That said you need to link your master table to the employee table on name - three times, once for each role. then you can see the email address.

[June7]

The employees table would have to be included in the query 3 times, 1 of each linked to one of the 3 fields (manager, engineer, sr engineer). Do not use INNER JOIN, use RIGHT JOIN. Bind textboxes to the fields from Employees table but do not allow edits in those, set Locked property to Yes.

Alternatively, the comboboxes can have additional columns with the employee info. Expressions in textboxes can reference columns of combobox by index. Index begins with 0, so like:

=[cbxManager].[Column](2)


EDIT: agree with Ajax - should be saving and linking on EmployeeID, not names.

[ssanfu]

And should not have given name and surname in the same field. Should be 2 fields. (Ex. FName, LName or FirstName, Surname)

[rcrooks]

Thanks for all your replies. I was a little short in giving information about my main table as there is roughly 30 fields. I appreciate your help and it has made me think differently about the problem.

I have create an "employeejob" table now. This links to the master table containing information about the job, and links to the employee containing information about the employee:

Master: SiteID, Site Name... etc
EmployeeJob: SiteID, EmployeeID, JobTitleID, JobID
Employee: EmployeeID, EmployeeName, EmployeeEmail, Department
JobTitles: JobTitleID,JobTitleName

This way I can assign an employee to a job on a site, and have many employees working on a site, and one employee working on many sites.

My relationships work spot on now, and it future proofs things if you need more people on site. I'm using a split form to pull up all the employees on a currents site (the master table is the record source on the main form), and grab their emails too.

Does this sound OK to proceed, or can you spot any potential pitfalls?

[June7]

What do you mean by 'split' form and 'main' form? Did you mean form/subform arrangement? A split form is a special type of form object.

[orange]

Getting your tables designed to support your business involves several things. It's easier if you have a good description of your business --no jargon --just plain English. You might want to "bounce your completed description off colleagues to ensure it is complete and understandable.

There are some tutorials at RogersAccessLibrary that lead you through a process from your business description through the identification of entities/tables,fields. keys and relationships. I recommend you work through 1 or 2 of these before jumping further into Access.
This one or one under the examples will help you with structure.

Good luck.