Solved

Table structure

Posted on 2010-11-16
6
537 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
6 Comments
 
LVL 84

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 84
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

685 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