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
Solved

Table structure

Posted on 2010-11-16
6
536 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

860 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