HR Relationship

[trburgess]

Hi I am trying to create the relationship for employees to their department and managers.

I have got the below:



I am struggling with how I link the Managers/Department/Employees.



Could someone point me in the right direction?

Regards,

Tom

[alansidman]

You have a many to many relationship and require a junction table to resolve this. Here is an excerpt from Microsoft on the issue

How to Define a Many-To-Many Relationships
To create a many-to-many relationship, follow these steps:
Create the two tables that will have a many-to-many relationship.
Create a third table, called a junction table, and then add to the junction table new fields with the same definitions as the primary key fields from each of the other two tables. In the junction table, the primary key fields function as foreign keys. You can add other fields to the junction table, just as you can to any other table.
In the junction table, set the primary key to include the primary key fields from the other two tables. For example, in an TitleAuthors junction table, the primary key would be made up of the OrderID and ProductID fields.

NOTE: To create a primary key, follow these steps:
Open a table in Design view.
Select the field or fields that you want to define as the primary key. To select one field, click the row selector for the desired field.

To select multiple fields, hold down the CTRL key, and then click the row selector for each field.
In Access 2002 or in Access 2003, click Primary Key on the toolbar.

In Access 2007, click Primary Key in the Tools group on the Design tab.

Note If you want the order of the fields in a multiple-field primary key to be different from the order of those fields in the table, click Indexes on the toolbar to display the Indexes dialog box, and then reorder the field names for the index named PrimaryKey.
Define a one-to-many relationship between each of the two primary tables and the junction table.

Alan

[trburgess]

Hi Alan,

Thank you for your response.

I understand what is required, but not sure how I would implement in this scenario.

Could you assist with an example?

Regards,

Tom Burgess

[recyan]

Was wondering the need for 2 seperate tables for Employee & Manager. Why not a single table?
Something like :

tblEmployees
EmployeeID - PK
Emp_First_Name
Emp_Last_Name
Department_ID
Manager_ID


In above table, the Manager_ID is the EmployeeID of another Employee in the same table.

Is there is a possibility of an Employee reporting to more than one Manager?
If yes, ignore my reply.

Thanks

[trburgess]

Hi,

I am not sure how to structure it. I need to be able to query all employees for a specific manager. Therefore I assumed I would need a separate table.

The other way of thinking was linking the manager to the department and then just linking the department to the employee.

That way I was thinking of adding a table:

depratmentEmployees to enable a many to many relationship between department and employees.

Any help for an adequate structure would be appreciated.

[recyan]

Hi,
I am not sure how to structure it. I need to be able to query all employees for a specific manager. Therefore I assumed I would need a separate table.

If you have an ideal situation of an employee reporting to one & only one Manager over a period of time, then we can use what I have suggested.
But I doubt whether such kind of ideal situation exists.
Before thinking of how to query & the forms & the reports, would advise to spend some time & thoughts on the tables & relationships.

Presenting you a few thoughts :
1) An employee
2) An employee can belong to different departments over a period of time
3) An employee can have one to many different Managers over a period of time
4) An employee can have one to many different Managers over a period of time while belonging to the same department also
5) An employee can be doing different JobTypes over a period of time
6) An employee can get same or different Salaries while doing the same JobType over a period of time

So
Employee
Department
JobType
Manager
Salary
Period


tblEmployees
EmployeeID
EmployeeName
EmployeeDateOfJoining

tblDepartments
DepartmentID
DepartmentName

tblJobTypes
JobTypeID
JobType

tblEmployeeEmploymentHistory
EmployeeEmploymentHistoryID
DateStart
DateEnd
Employee_ID
Department_ID
JobType_ID
Manager_ID
Salary

Pls note : These are just a few thoughts, so that you can start thinking about various possibilities & I may be totally off the mark, for all that I know.

Present all the possibilities that exist in your scenario & definitely there are a lot of experts here who will get you on the way.
One of them "Alan", you have already met.
Hi Alan

Thanks