Table structure

Hi there.
I am trying to develop an Employee database.
I have attached a jpeg showing what I have so far.
An employee can also be a chargehand (foreman), or a department head.
In tblEmployees the fields "DeptHead" and "ChargeHand" are number fields that are either 1 or 0.
1 meaning Yes and 0 meaning No.
I need to develop a report that shows Chargehands and the employees in their charge.
If I can do this, I could also adapt it to show department heads and people in the department.
I think I need a junction table, but I am not sure how to go about it. Maybe I need another table entirely.
Any help would be appreciated.
Thanks Relationships
Stephen ByromWarehouse/ShippingAsked:
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
I think you'd be better off with a "EmployeeType" field in tblEmployees, and build a new table that would hold your various EmployeeTypes (ChargeHand, Foreman, etc).

You'd then include a field in tblEmployees that would define who that employee would "report to" (perhaps named ReportsTo). This field would hold the EmployeeID of the person to whom they report (i.e. their boss). While a person can have multiple people in front of them in the business hierachy, in general there is only one direct supervisor.

If that's not the case, and if you need to define many different people to a single employee, then you will need a Junction table:


Both EmployeeID and SuperID would come from tblEmployees.
Difficult to add much to LMSConsulting's reply.

Database principle is to have one field for each attribute (type of employee with values saying what the attribute is (Chargehand, Foreman etc) as opposed to one field for each value of attribute as you suggested.

If (as is normal) employee (many) to boss (1) is many to 1, the link can be made directly, If an employee can have many bosses (as sadly seems to be the case in modern matrix organisations) you need a separate table to define these relationships. Each record in this table will link and employee to a boss/supervisor

In the 1 boss to many employees situation, Any record with employee type Chargehand will contain the Employee ID of that chargehand. A subsequent scan of the file for each record with this employeeID in the SuperID field will give you all employees with this person as a Chargehand
I think a simple solution would be to add a field called Manager_ID. That field would be the pkEmpID number of the person that this employee reports to. Then you can add filters to forms (filter by manager) do reports by manager or even an org chart.
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
jpateusa: I suggested this in my initial comment:

"You'd then include a field in tblEmployees that would define who that employee would "report to" (perhaps named ReportsTo). "
Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks for all the replies.
I have made a new look-up table, tlkpEmpType
I have also made a Junction table, jtblEmpSuper
In doing so the relationship window added another instance of tblEmployees.
I think this is what LSMConsulting meant.
It seems to make sense, or am I missing something?

See attached Jpeg
Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks for the help
All Courses

From novice to tech pro — start learning today.