Relating Multipe Tables With Similar Data To A Master Table

[TotalChaos]

I work for a small company that currently uses excel spreadsheets as timesheets. I am trying to design a database for timekeeping. We keep time on a weekly basis. We are a construction company that has site employees assigned to various projects. The timesheet entry for the projects is done by a site timekeeper. The site timekeeper may also enter subcontractors' personnel and occasionally new personnel who arrive on site and are hired thereafter. We also have a warehouse which has employees who do not work on projects. The timesheet entry for the warehouse is done by the warehouse timekeeper. Finally, we also have supervisory personnel who enter perform their own timesheet entry. I initially wanted to store all these personnel in one table but decided it may be better to store the different types of personnel in their own tables and relate them to a master personnel table that then relates to the time transactions table. Below is data from a spreadsheet which shows a sampling of table data using the same columns in each table to illustrate the differences between our types of personnel. I also included rules that govern each. In addition I am attaching a table relationship diagram from my 2007 MS Access file with the tables related as best I can. I am trying to find out if it is possible to link the individual personnel tables to one master table. Perhaps there is another, more practical approach to the design that I'm completely missing. Thanks for any help.



Also please see attachment.

Site Personnel
ID	EmplID	Name	Proj	Dept
1	111		1	Site
2	222		2	Site
3	333		1	Site
3	333		2	Site
Primary key is autonumber since a person can be assigned to more than one project,
Site Personnel are always assigned to projects.
Only user with "timesheet entry" privilege for dept "site" can enter time for site personnel.
Warehouse Personnel
ID	EmplID	Name	Proj	Dept
	444			Warehouse
	555			Warehouse
	666			Warehouse
Primary key is emplid
Warehouse personnel are not assigned to projects.
Only user with "timesheet entry" privilege for dept "warehouse" can enter time for warehouse personnel.
Supervision Personnel
ID	EmplID	Name	Proj	Dept
	777			Supervision
	888			Supervision
Primary key is emplid
Supervision personnel are not assigned to projects.
Only user with "timesheet entry" privilege for dept "supervision" can enter time for supervision personnel.
New Personnel
ID	EmplID	Name	Proj	Dept
1		New1	1	Site
2		New2	1	Site
3		New2	2	Site
4		New4		Warehouse
Primary key is autonumber since a person can be assigned to more than one project,
New personnel can be entered for site or warehouse depts.
Site Personnel are always assigned to projects.
Warehouse personnel are not assigned to projects.
Only user with "timesheet entry" privilege for dept "site" can enter time for site personnel.
Only user with "timesheet entry" privilege for dept "warehouse" can enter time for warehouse personnel.
Subcontractor Personnel
ID	EmplID	Name	Proj	Dept
1		Sub person 1	1	Site
2		Sub person 1	1	Site
3		Sub person 1	2	Site
4		Sub person 1		Warehouse
Primary key is autonumber since a person can be assigned to more than one project,
Subcontractor personnel can be entered for site or warehouse depts.
Site Personnel are always assigned to projects.
Warehouse personnel are not assigned to projects.
Only user with "timesheet entry" privilege for dept "site" can enter time for site subcontractors.
Only user with "timesheet entry" privilege for dept "warehouse" can enter time for warehouse subcontractors.

[Parsonshere]

I too, am having to write a time tracking program, but mine is not nearly as complicated as yours. I think you need to keep the name and individuals profiles in one table. However, past that, I think you will have to use different tables, only just use the Employee ID only in those tables. That way, if you hire a contractor and he becomes an employee instead, he doesn't have a new ID number, and you don't have to sort out separate ID's that he acquires over time. Association tables complicate things, but there are great blessing in them. Often these only have two numbers, an ID from your Employee, and then a project id or something. I wind up with four or five of these types of tables often in a single database. Also think about warehouse type data in temporary tables. You just have to lock them in the form so they can't be edited and used for info only. This is where you build temporary table from a query with all the fields you need just for the temporary report, or form based task that you select from a listbox or command button or something. They are way easier and faster to work with, than trying to do all the linking from different tables for the record source. I am looking at these relationships, and I have almost the same thing. I have an IT class to present on Tue, after that I will be working on mine. I will keep an eye on this post.