A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Some Employees work at all facilities



 
 
Thread Tools Display Modes
  #1  
Old March 10th, 2005, 04:13 AM
LMB
external usenet poster
 
Posts: n/a
Default Some Employees work at all facilities

Hi Guys,

Suffice it to say I am a beginner. I have made and edited some databases with help from the group and now am attempting to create a new "by the rules" employee database. This is a database to keep track of the employees in the respiratory department. We have 3 facilities. Each facility has a respiratory department. Some employees only work at once facility but there are some that work at 2 or all three. Right now I have 3 separate databases for each facility. I may keep it that way but I was hoping to use the demographics portion of the Employee table for all of the facilities. I am stuck on where the hire dates would go. This is what I have on paper so far but it just doesn't look right. These are the hire dates each employee can have.

MHPHireDate (date employee was hired in system) Possible to have only 1 per employee
FacilityHireDate (date employee was hired at facility) Possible to have 3 for an employee
DeptHireDate (date employee was hired in specific Department) Possible to have 3 per emp.

It may be confusing that facility and dept could be different but an employee could have been hired at a facility in the dietary dept then transfer to the respiratory dept.

These dates determine seniority for different events that will be tracked in the database.

This is what I have so far...

tblEmployee
EmployeeID
EmployeeLName
EmployeeFName
MHPHireDate

tbllstFacility
FacilityID
FacilityName

tblEmployeeFacility
EmployeeFacilityID
EmployeeID
FacilityHireDate
DepartmentHireDate

Do I need to put Department in a different table?



Thanks,
Linda



  #2  
Old March 10th, 2005, 02:21 PM
BruceM
external usenet poster
 
Posts: n/a
Default

I will assume that the field named (Something)ID in the first two tables is
the primary key (PK) field. Since each employee can work at many (i.e. more
than one) facilities, and each facility can have many employees working
there, there is a many to many relationship between employees and facilities.
To resolve that you will need a junction table. I suggest the following
modifications to tblEmployeeFacility (FK stands for foreign key).

tblEmployeeFacility
EmployeeFacilityID (PK)
EmployeeID (FK)
FacilityID (FK)
FacilityHireDate

Establish a relationship between the PKs from tblEmployee and tblFacility
and the FK fields from tblEmployeeFacility. Remember that you define the PK
in table design, but a field becomes the FK because of its relationship to
the PK. Also, if the PKs are of the data type Autonumber the FKs need to be
Number; If the PKs are other than autonumber, the FKs need to be the same.
You may already know this, but I don't know that you know.
Department should probably be a separate table linked to tblFacility and
tblEmployee. If each employee in their careers will work at three
departments maximum you could keep that information in tblEmployeeFacility.
However, if an employee could possibly work at more than three departments,
you're sunk. Even if it seems impossible now, I really think it is best to
allow for that. tblDepartment would be constructed similarly to
tblEmployeeFacility, except the relationship would be to tblEmployeeFacility
rather than to tblFacility.
This approach involves forms and subforms for entering and viewing data. If
the structure suggested works for you and you need assistance setting up the
forms and subforms, post back with your questions.



"LMB" wrote:

Hi Guys,

Suffice it to say I am a beginner. I have made and edited some databases with help from the group and now am attempting to create a new "by the rules" employee database. This is a database to keep track of the employees in the respiratory department. We have 3 facilities. Each facility has a respiratory department. Some employees only work at once facility but there are some that work at 2 or all three. Right now I have 3 separate databases for each facility. I may keep it that way but I was hoping to use the demographics portion of the Employee table for all of the facilities. I am stuck on where the hire dates would go. This is what I have on paper so far but it just doesn't look right. These are the hire dates each employee can have.

MHPHireDate (date employee was hired in system) Possible to have only 1 per employee
FacilityHireDate (date employee was hired at facility) Possible to have 3 for an employee
DeptHireDate (date employee was hired in specific Department) Possible to have 3 per emp.

It may be confusing that facility and dept could be different but an employee could have been hired at a facility in the dietary dept then transfer to the respiratory dept.

These dates determine seniority for different events that will be tracked in the database.

This is what I have so far...

tblEmployee
EmployeeID
EmployeeLName
EmployeeFName
MHPHireDate

tbllstFacility
FacilityID
FacilityName

tblEmployeeFacility
EmployeeFacilityID
EmployeeID
FacilityHireDate
DepartmentHireDate

Do I need to put Department in a different table?



Thanks,
Linda



  #3  
Old March 10th, 2005, 03:47 PM
Jamie Collins
external usenet poster
 
Posts: n/a
Default

tblEmployeeFacility
EmployeeFacilityID (PK)
EmployeeID (FK)
FacilityID (FK)
FacilityHireDate


I'd suggest a unique constraint on (FacilityID,
EmployeeID) or make this the PK, even.

Jamie.

--




  #4  
Old March 12th, 2005, 10:35 PM
LMB
external usenet poster
 
Posts: n/a
Default

Thanks Bruce,

I hope to get some time tonight to draw this out. Just had 3 days of 16 hour shifts. Yes, the top SomethingID is my primary key and is an autonumber. I did actually know about the FK needing to be a number but I am glad you included that. I learned this the hard way.

The rest of the tables in the database will have the same field names but the data will be specific for each employee at a specific facility. An example is something we call the clinical ladder level. Each facility has 4 levels but an employee may be a level 3 at one facility and a 2 at another. Will this change the way I have to structure the database below? An idea came to mind and I was thinking that perhaps I would have the tables that were the same for each employee in one database and then have 3 separate databases that link to these tables. Is it possible to link to data in a query?

BTW, I think I discovered how some of you guys reduce the post size by taking out the headers....Learned something else from you.

Linda
"BruceM" wrote:

I will assume that the field named (Something)ID in the first two tables is
the primary key (PK) field. Since each employee can work at many (i.e. more
than one) facilities, and each facility can have many employees working
there, there is a many to many relationship between employees and facilities.
To resolve that you will need a junction table. I suggest the following
modifications to tblEmployeeFacility (FK stands for foreign key).

tblEmployeeFacility
EmployeeFacilityID (PK)
EmployeeID (FK)
FacilityID (FK)
FacilityHireDate

Establish a relationship between the PKs from tblEmployee and tblFacility
and the FK fields from tblEmployeeFacility. Remember that you define the PK
in table design, but a field becomes the FK because of its relationship to
the PK. Also, if the PKs are of the data type Autonumber the FKs need to be
Number; If the PKs are other than autonumber, the FKs need to be the same.
You may already know this, but I don't know that you know.
Department should probably be a separate table linked to tblFacility and
tblEmployee. If each employee in their careers will work at three
departments maximum you could keep that information in tblEmployeeFacility.
However, if an employee could possibly work at more than three departments,
you're sunk. Even if it seems impossible now, I really think it is best to
allow for that. tblDepartment would be constructed similarly to
tblEmployeeFacility, except the relationship would be to tblEmployeeFacility
rather than to tblFacility.
This approach involves forms and subforms for entering and viewing data. If
the structure suggested works for you and you need assistance setting up the
forms and subforms, post back with your questions.



"LMB" wrote:

Hi Guys,

Suffice it to say I am a beginner. I have made and edited some databases with help from the group and now am attempting to create a new "by the rules" employee database. This is a database to keep track of the employees in the respiratory department. We have 3 facilities. Each facility has a respiratory department. Some employees only work at once facility but there are some that work at 2 or all three. Right now I have 3 separate databases for each facility. I may keep it that way but I was hoping to use the demographics portion of the Employee table for all of the facilities. I am stuck on where the hire dates would go. This is what I have on paper so far but it just doesn't look right. These are the hire dates each employee can have.

MHPHireDate (date employee was hired in system) Possible to have only 1 per employee
FacilityHireDate (date employee was hired at facility) Possible to have 3 for an employee
DeptHireDate (date employee was hired in specific Department) Possible to have 3 per emp.

It may be confusing that facility and dept could be different but an employee could have been hired at a facility in the dietary dept then transfer to the respiratory dept.

These dates determine seniority for different events that will be tracked in the database.

This is what I have so far...

tblEmployee
EmployeeID
EmployeeLName
EmployeeFName
MHPHireDate

tbllstFacility
FacilityID
FacilityName

tblEmployeeFacility
EmployeeFacilityID
EmployeeID
FacilityHireDate
DepartmentHireDate

Do I need to put Department in a different table?



Thanks,
Linda



  #5  
Old March 12th, 2005, 10:37 PM
LMB
external usenet poster
 
Posts: n/a
Default

Jamie,

What is a unique constraint?

Thanks,
Linda
"Jamie Collins" wrote:

tblEmployeeFacility
EmployeeFacilityID (PK)
EmployeeID (FK)
FacilityID (FK)
FacilityHireDate


I'd suggest a unique constraint on (FacilityID,
EmployeeID) or make this the PK, even.

Jamie.

--




  #6  
Old March 14th, 2005, 09:14 AM
Jamie Collins
external usenet poster
 
Posts: n/a
Default


LMB wrote:
What is a unique constraint?


See:

Microsoft Jet SQL Reference: CONSTRAINT Clause
http://office.microsoft.com/en-us/as...322141033.aspx

BruceM's design has nothing to prevent the following:

INSERT INTO tblEmployeeFacility
(EmployeeFacilityID, EmployeeID, FacilityID, FacilityHireDate)
VALUES (1,1,1,DATE());

multiple times, possibly with different dates.

Jamie.

--

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem With Nested Joins Don Running & Setting Up Queries 8 February 26th, 2005 10:04 PM
Calendars in OL2003/OL2000 Rich General Discussion 16 February 10th, 2005 01:41 AM
"Work Offline" Menu and "Exit" Menu MSHUME General Discussion 1 September 27th, 2004 09:17 PM
Excel work sheet Dagmar Kuehl Worksheet Functions 1 September 22nd, 2004 09:12 AM
Finding labor total for multimple employees in 15 min. increments. seggallion General Discussion 1 July 9th, 2004 05:52 AM


All times are GMT +1. The time now is 10:23 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.