Solved

Table structure

Posted on 2010-11-16
6
539 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Stephen Byrom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34144124
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:

tblEmployee_Supervisors
---------------------------
pkEmpSupID
EmployeeID
SuperID

Both EmployeeID and SuperID would come from tblEmployees.
0
 
LVL 3

Expert Comment

by:Rincewindwizz
ID: 34144450
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
0
 

Expert Comment

by:jpateusa
ID: 34144469
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.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 85
ID: 34144487
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). "
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34148114
Ok,
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
Dbase.jpg
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 34158802
Thanks for the help
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question