Solved

Table structure

Posted on 2010-11-16
6
535 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

776 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