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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem With Nested Joins | Don | Running & Setting Up Queries | 8 | February 26th, 2005 09:04 PM |
Calendars in OL2003/OL2000 | Rich | General Discussion | 16 | February 10th, 2005 12: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 |